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
• DB2/SQL Functions:– Aggregate Functions cont.:
• MINselect min(PHTPRM)
from dlyfile.polhdr
where phccde not in ('D','V')
and phtprm <> 0;
select phstno||phtype||phnumb as POLNBR
, phnam1, phtprm
from dlyfile.polhdr
where phtprm =
(
select min(PHTPRM)
from dlyfile.polhdr
where phccde not in ('D','V')
and phtprm <> 0
) ;
9.75
POLNBR PHNAM1 PHTPRM43P123456 WALLACE BAPTIST ASSEMBLY 9.75
• DB2/SQL Functions:– Aggregate Functions cont.:
• SUM
select phtype, sum(PHTPRM) as PREM_TOTS
from dlyfile.polhdr
where phccde not in ('D','V')
and phstno = 11
group by phtype
order by 2 desc;
PHTYPE PREM_TOTSM 25000A 11000W 9000P 3500
• DB2/SQL Functions:– Aggregate Functions cont.:
• 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:
• 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
where phccde not in ('D','V')
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_
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• CONCAT
select concat(concat(phstno,phtype),phnumb) as POLNBR
from dlyfile.polhdr
where phtprm =
(
select max(PHTPRM)
from dlyfile.polhdr
where phccde not in ('D','V')
) ;
OR select phstno||phtype||phnumb as POLNBR
from dlyfile.polhdr
where phtprm =
(
select max(PHTPRM)
from dlyfile.polhdr
where phccde not in ('D','V')
) ;
11M987654
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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
where phccde not in ('D','V')
and phtprm <> 0;
Smiddy Smiddy Smit Smit Smith Smith Smith
000000001663920 1663920.00000000002029023 2029023.00000000000642056 642056.00000000000035577 35577.00
9
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• RRN
select rrn(prdsql.acc) as rrn, accnam
from prdsql.acc
where lower(accnam) like '%bruce%'
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
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• 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.CRKYTXT2 = TB2.CRKYTXT2
and TB1.CRKYNMB2 = TB2.CRKYNMB2
and TB1.CRKYTXT3 = TB2.CRKYTXT3
and TB1.CRKYNMB3 = TB2.CRKYNMB3
and TB1.CRKYTXT4 = TB2.CRKYTXT4
and TB1.CRKYNMB4 = TB2.CRKYNMB4
and TB1.CREFFDT = TB2.CREFFDT
);
• R
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• STRIP
select accnam, length(accnam)
from prdsql.acc
where lower(accnam) like '%bruce%'
and lower(accnam) like '%test%';
select accnam, length(strip(accnam,T,' '))
from prdsql.acc
where lower(accnam) like '%bruce%'
and lower(accnam) like '%test%';
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
Independent Test Church For Bruce 33Test Account By Bruce 21Test Church By Bruce 20A Missouri Test Bruce 21
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• TRIM
select accnam, length(accnam)
from prdsql.acc
where lower(accnam) like '%bruce%'
and lower(accnam) like '%test%';
select accnam, length(trim(T ' ' from accnam))
from prdsql.acc
where lower(accnam) like '%bruce%'
and lower(accnam) like '%test%';
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
Independent Test Church For Bruce 33Test Account By Bruce 21Test Church By Bruce 20A Missouri Test Bruce 21
• DB2/SQL Functions:– Scalar Functions Examples, Cont.:
• SUBSTR
select accnam, substr(accnam,5,10)
from prdsql.acc
where lower(accnam) like '%bruce%'
and lower(accnam) like '%test%';
• 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:
• Visual Explain:
• Visual Explain – Index Advisor:
• Visual Explain – Index Advisor:
• Visual Explain – Index Advisor:
Clicking on ‘OK’ creates the index.
• Visual Explain – Index Advisor:
Clicking on ‘OK’ creates the index.
• 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
The End