db2 and sql. agenda: –why use sql? –rewrite opnqryf example –db2 built in functions...

DB2 and SQL

• Agenda:

– Why use SQL?

– Rewrite OPNQRYF Example

– DB2 Built In Functions

– Miscellaneous SQL Examples.

– Visual Explan Example.

• Why Convert To SQL:– Widespread Use In Marketplace

• All Other Database platforms use SQL.• SQL standard for database access.• Very similar between platforms – SQL Server, Oracle, DB2 Z/OS, DB2 LUW, iSeries.

– SQE gets all the enhancements• CQE not being enhanced.• Performance improvements constantly being added to SQE.• Performance tools within iSeries Navigator use SQL/SQE.

– SQL gets all the enhancements• DDS development non-existent.• Scalar functions

• Rewrite OPNQRYF:– Example:– Remove From CL:

OVRDBF FILE(EASUNE) SHARE(*YES)

OPNQRYF FILE(EASUNE) QRYSLT('UEPERL *EQ "63" +

*OR UEPERL *EQ "64" *OR UEPERL *EQ +

"69"')

/* Load Unearned Prem Counts */

CALL PGM(MC0060)

CLOF OPNID(EASUNE)

DLTOVR FILE(EASUNE)

• Rewrite OPNQRYF:– Update RPG:

* SQL Error Routine

d WriteSQLERR pr

d 10 const

d 10i 0 const

d 5 const

d 2002 const

d wkarea ds

d sq_UESRC 5a

d sq_UEMEDT 8s 0

d sq_UEST 2a

d sq_UEPERL 2a

d sq_UEACCT 4s 0

d sq_UECOST 3s 0

d sq_UEAMT 15a

d sq_UEDBCR 1a

• Rewrite OPNQRYF:– Update RPG:

begsr SELrecs;

@sqlstmt = 'select * '+

'from EASUNE '+ ==== Replaces OPNQRYF

'where UEPERL IN(''63'',''64'',''69'') ';

exec sql prepare ssqlchk2 from :@sqlstmt;

exec sql declare csqlchk2 cursor for ssqlchk2;

exec sql open csqlchk2 ;

• Rewrite OPNQRYF:– Update RPG, cont.:

dou sqlcod = 100;

exec sql fetch csqlchk2 into :wkarea :SQLInd;

select;

when sqlcod = 100;

LEAVE;

when sqlcod = 0;

exsr PRCrecs;

when sqlcod < 0;

WriteSQLERR(ProgName:sqlcod:sqlstate:@sqlstmt);

endsl;

enddo;

exec sql close csqlchk2;

endsr;

• Rewrite OPNQRYF:– Notes:

• PRCrecs Subroutine .– Subroutine consists of “old” code.

• Other Minor Changes:– Variables updated.

– Overlay statements removed and changed to SUBSTR:» OLD CODE:

dmedate ds

d uemedt 8s 0 overlay(medate:1)

d uemm 2 overlay(medate:1)

d uedd 2 overlay(medate:3)

d uey4 4 overlay(medate:5)

.

.

month = uemm;

day = uedd;

year = uey4;

• Rewrite OPNQRYF:– Notes, cont.:

– UPDATED CODE:

uemedtwk = %char(sq_uemedt);

month = %subst(uemedtwk:1:2);day = %subst(uemedtwk:3:2);year = %subst(uemedtwk:5:4);

- EASUNE Changed From Input Primary.

• DB2/SQL Functions:– Built-in Functions: Come with DBMS

• Operator Functions – +, -, *, /

• Aggregate functions– Takes sets of values (column of data) and returns a single result.– Examples to follow

• Scalar functions– Takes input arguments and returns a single value result.– Examples to follow– Roughly 160 in v6.1

• DB2/SQL Functions:– Aggregate Functions (not all inclusive):

• AVG

select avg(data_size)/1024/1024

from qsys2.systablestat

where table_schema = 'PRDSQL'

;

• COUNT

select count(*) from dlyfile.nit3 ;

select count(*) as count,a3type

from dlyfile.nit3

group by a3type

order by 1 desc;

234

8549

count a3type5000 M2000 A1500 W

37 P12 S

• DB2/SQL Functions:– Aggregate Functions cont.:

• MAXselect max(PHTPRM)

from dlyfile.polhdr

where phccde not in ('D','V') ;

select phstno||phtype||phnumb as POLNBR

,phnam1, phtprm

from dlyfile.polhdr

where phtprm =

(

select max(PHTPRM)

from dlyfile.polhdr

where phccde not in ('D','V')

) ;

2800

POLNBR PHNAM1 PHTPRM11M987654 Wallace United Methodist 2800


• MINselect min(PHTPRM)

from dlyfile.polhdr


and phtprm <> 0;

select phstno||phtype||phnumb as POLNBR

, phnam1, phtprm

from dlyfile.polhdr

where phtprm =

(

select min(PHTPRM)

from dlyfile.polhdr


and phtprm <> 0

) ;

9.75

POLNBR PHNAM1 PHTPRM43P123456 WALLACE BAPTIST ASSEMBLY 9.75


• SUM

select phtype, sum(PHTPRM) as PREM_TOTS

from dlyfile.polhdr


and phstno = 11

group by phtype

order by 2 desc;

PHTYPE PREM_TOTSM 25000A 11000W 9000P 3500


• Other Aggregate Functions:

– STDDEV

– STDDEV_SAMP

– VAR

– VAR_SAMP

• DB2/SQL Functions:– Scalar Functions (not all inclusive):

• Types:

– Mathematical – Date/Time– String

• Reference Link:

– http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscale.htm?resultof=%22%73%63%61%6c%61%72%22%20%22%66%75%6e%63%74%69%6f%6e%73%22%20%22%66%75%6e%63%74%69%6f%6e%22%20

• Examples:

http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscale.htm?resultof=%22scalar%22%20%22functions%22%20%22function%22%20



• DB2/SQL Functions:– Scalar Functions Examples:

• ADD_MONTHS:

select add_months(current date, 5)

from sysibm.sysdummy1;

• CEILING:

select ceiling(min(PHTPRM))

from dlyfile.polhdr


and phtprm <> 0;

9/3/2013

10

• DB2/SQL Functions:– Scalar Functions Examples, Cont.:

• CHAR

select objname

select char(objname,35)

from ACMSCTL.OBJENV

where

objtype = '*TABLE'

and objattr = 'SQL'

and objname = 'TABLE_TO_SERVE_AS_BASE_FOR_CERTAIN_BLOCKING_OBJECTS’;

TABLE_TO_SERVE_AS_BASE_FOR_CERTAIN_BLOCKING_OBJECTS

TABLE_TO_SERVE_AS_BASE_FOR_CERTAIN_


• CONCAT

select concat(concat(phstno,phtype),phnumb) as POLNBR

from dlyfile.polhdr

where phtprm =

(

select max(PHTPRM)

from dlyfile.polhdr


) ;

OR select phstno||phtype||phnumb as POLNBR

from dlyfile.polhdr

where phtprm =

(

select max(PHTPRM)

from dlyfile.polhdr


) ;

11M987654


• CURDAT

SELECT curdate()

FROM SYSIBM.SYSDUMMY1;

• CURTIM

SELECT curtime()

FROM SYSIBM.SYSDUMMY1

;

• DATABASE

SELECT DATABASE( )

FROM SYSIBM.SYSDUMMY1

;

4/3/2013

15.44.22

S102921C


• DATE

select current timestamp

from sysibm.sysdummy1 ==== 2013-04-03 15:51:51.220055

;

select date(current timestamp)

from sysibm.sysdummy1 ==== 2013-04-03

;

• DAY

select day(current timestamp)

from sysibm.sysdummy1 ==== 3

;

• DAYNAME

select dayname(current timestamp)

from sysibm.sysdummy1 ===

; Wednesday


• DAYOFWEEK (1 -> 7; 1=Sunday; 7=Saturday)

select dayofweek(current timestamp + 9 days)

from sysibm.sysdummy1

;

• DAYOFWEEK_ISO (1 -> 7; 1=Monday; 7=Sunday)

select dayofweek_iso(current timestamp + 9 days)

from sysibm.sysdummy1

;

6

5


• SOUNDEX

select ennaml

from dlyfile.crentity

where soundex(ennaml) = soundex('smythe');

• DECIMAL

select ueamt, dec(ueamt,15,2) from

MTHFILE.EASUNE;

• FLOOR

select floor(min(PHTPRM))

from dlyfile.polhdr


and phtprm <> 0;

Smiddy Smiddy Smit Smit Smith Smith Smith

000000001663920 1663920.00000000002029023 2029023.00000000000642056 642056.00000000000035577 35577.00

9


• HOUR

select hour(current timestamp) from

sysibm.sysdummy1;

• MINUTE

select minute(current timestamp) from

sysibm.sysdummy1;

• SECOND

select second(current timestamp) from

sysibm.sysdummy1;

10

39

3


• MICROSECOND

select microsecond(current timestamp) from

sysibm.sysdummy1;

• LEFT

select accnam,left(accnam,10)

from ua2sql.acc

where lower(accnam) like '%bruce%’;

• RIGHT

select accnam,right(accnam,10)

from ua2sql.acc

where lower(accnam) like '%bruce%‘;

A Bruce Quote A Bruce Qu

Faith United Church Attn Bruce Whitaker e Whitaker

567191


• LENGTH

select accnam,length(accnam)

from ua2sql.acc

where lower(accnam) like '%bruce%’;

• LOWER

select accnam

from ua2sql.acc

where accnam like '%bruce% === returns 0 rows

where lower(accnam) like '%bruce%’; = Returns Data

• UPPER

select accnam

from ua2sql.acc

where accnam like '%bruce% === returns 0 rows

where UPPER(accnam) like '%BRUCE%’; = Returns Data

Lighthouse Tabernacle Attn Bruce Benson 40Ancient Paths Church Attn Bruce Duell 37Faith United Church Attn Bruce Whitaker 39


• MONTHS_BETWEEN

select months_between(max(icpdt) , min(icpdt))

from prdsql.pol

where year(icpdt) > '2007' and year(icpdt) < '2014'

• ROUND

select round(months_between(max(icpdt) , min(icpdt)),2)

from prdsql.pol

where year(icpdt) > '2007' and year(icpdt) < '2014‘

• REPLACE

select accnam

,replace(accnam,'Bruce','Wallace')

from ua2sql.acc

where lower(accnam) like '%bruce%'

68.5483871

68.55

Test Account By Bruce

Test Account By Wallace

Test Church By Bruce

Test Church By Wallace

A Missouri Test Bruce

A Missouri Test Wallace


• RRN

select rrn(prdsql.acc) as rrn, accnam

from prdsql.acc


and lower(accnam) like '%test%';36345

Independent Test Church For Bruce

37304

Test Account By Bruce

37913

Test Church By Bruce

39229

A Missouri Test Bruce


• RRN – Practical Example – Duplicate Row Cleanup

Delete from MSCDTA.carates TB1 where RRN(TB1) >

(

select MIN(RRN(TB2)) from MSCDTA.carates TB2

where TB1.CRCAT = TB2.CRCAT

and TB1.CRSTNO = TB2.CRSTNO

and TB1.CRKYTXT1 = TB2.CRKYTXT1

and TB1.CRKYNMB1 = TB2.CRKYNMB1







and TB1.CREFFDT = TB2.CREFFDT

);

• R


• STRIP

select accnam, length(accnam)

from prdsql.acc


and lower(accnam) like '%test%';

select accnam, length(strip(accnam,T,' '))

from prdsql.acc



Note: T = Trailing ; Other options are L (Leading) and B (Both)

Independent Test Church For Bruce 150Test Account By Bruce 150Test Church By Bruce 150A Missouri Test Bruce 150



• TRIM

select accnam, length(accnam)

from prdsql.acc



select accnam, length(trim(T ' ' from accnam))

from prdsql.acc



Note: T = Trailing ; Other options are L (Leading) and B (Both)




• SUBSTR

select accnam, substr(accnam,5,10)

from prdsql.acc



• COALESCE

select name,salary

from swallace.sal;

select name,coalesce(salary, 0)

from swallace.sal;

Independent Test Church For Bruce pendent TeTest Account By Bruce Account BTest Church By Bruce Church ByA Missouri Test Bruce ssouri Tes

Steve nullMolly nullHannah 15000

Steve 0Molly 0Hannah 15000

• Other SQL Examples:– SQL that builds other SQL statements:

select 'label on table '||strip(a.system_table_schema)||'.'||a.system_table_name|| 'IS '''||strip(b.table_text)||''';'

from

qsys2.systables a,

qsys2.systables b

where a.system_table_schema='UTESQL'

and b.system_table_schema='UPGSQL'

and a.system_table_name=b.system_table_name

and a.system_table_name not like 'SYS%'

and (a.table_text = ' ' or a.table_text is null)

order by a.system_table_name

;

label on table UTESQL.ACC IS 'Account Table';label on table UTESQL.ACCADR IS 'Account Address';label on table UTESQL.ACCADRH IS 'Account Address History';label on table UTESQL.ACCAGC IS 'Account Agency';label on table UTESQL.ACCAGCH IS 'Account Agency History';label on table UTESQL.ACCCBDVIEW IS 'Account Combined View';label on table UTESQL.ACCCOM IS 'Account Communication';label on table UTESQL.ACCCOMH IS 'Account Communication History';label on table UTESQL.ACCCRM IS 'Account Crm';

• Other SQL Examples:– SQL To Query Journal Entries:

select entry_timestamp,journal_code,journal_entry_type, char(entry_data,500),object ,

program_name from table (Display_Journal(

'UTESQL', 'QSQJRN', -- Journal library and name

'UTESQL','QSQJRN0126', -- Receiver library and name

CAST('2013-04-08-06.44.59.999999' as TIMESTAMP), -- Starting timestamp

CAST(null as DECIMAL(21,0)), -- Starting sequence number

'', -- Journal codes

'', -- Journal entry type

'','', -- Object library and name, Object Type

'','', -- Object type, Object member

'', -- User

'', -- Job

'' -- Program

) ) as x

where object like '%POL %'

and entry_timestamp between '2013-04-08 06:44:59.999999' and '2013-04-08 11:19:59.999999'

--and journal_entry_type in ('RB','BR','DR','UR')

--and substr(char(entry_data,1000),1,9) = '001010917'

order by 1;

• Other SQL Examples:– SQL To Query Journal Entries:

select entry_timestamp,journal_code,journal_entry_type, char(entry_data,500),object ,

program_name from table (Display_Journal(

'UTESQL', 'QSQJRN', -- Journal library and name

'UTESQL','QSQJRN0126', -- Receiver library and name

CAST('2013-04-08-06.44.59.999999' as TIMESTAMP), -- Starting timestamp

CAST(null as DECIMAL(21,0)), -- Starting sequence number

'', -- Journal codes

'', -- Journal entry type

'','', -- Object library and name, Object Type

'','', -- Object type, Object member

'', -- User

'', -- Job

'' -- Program

) ) as x

where object like '%POL %'

and entry_timestamp between '2013-04-08 06:44:59.999999' and '2013-04-08 11:19:59.999999'

--and journal_entry_type in ('RB','BR','DR','UR')

and substr(char(entry_data,1000),1,9) = '001010917'

order by 1;

• Visual Explain:– Graphical representation of how the DB2 optimizer satisfies the SQL request.

– Select, Insert, Update, Delete.

– Invoked through iSeries Navigator (most common), or through the Visual Explain (QQQVEXPL) API.

– Highlight expensive operation

– Recommend Indexes, Statistics, or Both.

• Visual Explain:

• Visual Explain – Index Advisor:

• Visual Explain – Index Advisor:

Clicking on ‘OK’ creates the index.

References- IBM I 6.1 Information Center:

- http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/index.jsp

http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/index.jsp

http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/index.jsp

The End

db2 and sql. agenda: –why use sql? –rewrite opnqryf example –db2 built in functions...

Documents