Transcript MONOLINE WC - Status An IBM Midrange User's Group

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
d sq_UEMEDT
d sq_UEST
d sq_UEPERL
d sq_UEACCT
d sq_UECOST
d sq_UEAMT
d sq_UEDBCR
5a
8s 0
2a
2a
4s 0
3s 0
15a
1a
• Rewrite OPNQRYF:
– Update RPG:
begsr SELrecs;
@sqlstmt = 'select *
'from EASUNE
'where UEPERL IN(''63'',''64'',''69'')
exec sql prepare ssqlchk2 from :@sqlstmt;
exec sql declare csqlchk2 cursor for ssqlchk2;
exec sql open csqlchk2 ;
'+
'+
';
====
Replaces OPNQRYF
• 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'
;
234
• COUNT
select count(*) from dlyfile.nit3 ;
select count(*) as count,a3type
from dlyfile.nit3
group by a3type
order by 1 desc;
8549
count a3type
5000
M
2000
A
1500
W
37
P
12
S
• DB2/SQL Functions:
– Aggregate Functions cont.:
• MAX
select 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
11M987654 Wallace United Methodist
PHTPRM
2800
• DB2/SQL Functions:
– Aggregate Functions cont.:
• MIN
select min(PHTPRM)
from dlyfile.polhdr
where phccde not in ('D','V')
and phtprm <> 0;
9.75
select phstno||phtype||phnumb as POLNBR
, phnam1, phtprm
from dlyfile.polhdr
POLNBR
PHNAM1
43P123456 WALLACE BAPTIST ASSEMBLY
where phtprm =
(
select min(PHTPRM)
from dlyfile.polhdr
where phccde not in ('D','V')
and phtprm <> 0
);
PHTPRM
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
M
A
W
P
PREM_TOTS
25000
11000
9000
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%6
3%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;
9/3/2013
• CEILING:
select ceiling(min(PHTPRM))
from dlyfile.polhdr
where phccde not in ('D','V')
and phtprm <> 0;
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;
4/3/2013
• CURTIM
SELECT curtime()
FROM SYSIBM.SYSDUMMY1
;
15.44.22
• DATABASE
SELECT DATABASE( )
FROM SYSIBM.SYSDUMMY1
;
S102921C
• DB2/SQL Functions:
– Scalar Functions Examples, Cont.:
• DATE
select current timestamp
from sysibm.sysdummy1
====
;
select date(current timestamp)
from sysibm.sysdummy1
====
;
2013-04-03 15:51:51.220055
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
;
6
• DAYOFWEEK_ISO (1 -> 7; 1=Monday; 7=Sunday)
select dayofweek_iso(current timestamp + 9 days)
from sysibm.sysdummy1
;
5
• DB2/SQL Functions:
– Scalar Functions Examples, Cont.:
• SOUNDEX
select ennaml
from dlyfile.crentity
where soundex(ennaml) = soundex('smythe');
Smiddy
Smiddy
Smit
Smit
Smith
Smith
Smith
• DECIMAL
select ueamt, dec(ueamt,15,2) from
MTHFILE.EASUNE;
000000001663920
000000002029023
000000000642056
000000000035577
1663920.00
2029023.00
642056.00
35577.00
• FLOOR
select floor(min(PHTPRM))
from dlyfile.polhdr
where phccde not in ('D','V')
and phtprm <> 0;
9
• DB2/SQL Functions:
– Scalar Functions Examples, Cont.:
• HOUR
select hour(current timestamp) from
sysibm.sysdummy1;
10
• MINUTE
select minute(current timestamp) from
sysibm.sysdummy1;
39
• SECOND
select second(current timestamp) from
sysibm.sysdummy1;
3
• DB2/SQL Functions:
– Scalar Functions Examples, Cont.:
• MICROSECOND
select microsecond(current timestamp) from
sysibm.sysdummy1;
567191
• LEFT
select accnam,left(accnam,10)
from ua2sql.acc
where lower(accnam) like '%bruce%’;
A Bruce Quote A Bruce Qu
• RIGHT
select accnam,right(accnam,10)
from ua2sql.acc
where lower(accnam) like '%bruce%‘;
Faith United Church Attn Bruce Whitaker
e Whitaker
• DB2/SQL Functions:
– Scalar Functions Examples, Cont.:
• LENGTH
select accnam,length(accnam)
from ua2sql.acc
where lower(accnam) like '%bruce%’;
Lighthouse Tabernacle Attn Bruce Benson
Ancient Paths Church Attn Bruce Duell
Faith United Church Attn Bruce Whitaker
• 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
40
37
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'
68.5483871
• ROUND
select round(months_between(max(icpdt) , min(icpdt)),2)
from prdsql.pol
where year(icpdt) > '2007' and year(icpdt) < '2014‘
68.55
• REPLACE
select accnam
,replace(accnam,'Bruce','Wallace')
from ua2sql.acc
where lower(accnam) like '%bruce%'
Test Account By Bruce
Test Church By Bruce
A Missouri Test Bruce
Test Account By Wallace
Test Church By Wallace
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
);
• 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%';
Independent Test Church For Bruce
Test Account By Bruce
Test Church By Bruce
A Missouri Test Bruce
150
150
150
150
select accnam, length(strip(accnam,T,' '))
from prdsql.acc
where lower(accnam) like '%bruce%'
and lower(accnam) like '%test%';
Independent Test Church For Bruce
Test Account By Bruce
Test Church By Bruce
A Missouri Test Bruce
33
21
20
21
Note: T = Trailing ; Other options are L (Leading) and B (Both)
• 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%';
Independent Test Church For Bruce
Test Account By Bruce
Test Church By Bruce
A Missouri Test Bruce
150
150
150
150
select accnam, length(trim(T ' ' from accnam))
from prdsql.acc
where lower(accnam) like '%bruce%'
and lower(accnam) like '%test%';
Independent Test Church For Bruce
Test Account By Bruce
Test Church By Bruce
A Missouri Test Bruce
33
21
20
21
Note: T = Trailing ; Other options are L (Leading) and B (Both)
• 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%';
Independent Test Church For Bruce
Test Account By Bruce
Test Church By Bruce
A Missouri Test Bruce
pendent Te
Account B
Church By
ssouri Tes
• COALESCE
select name,salary
from swallace.sal;
Steve
Molly
Hannah
select name,coalesce(salary, 0)
from swallace.sal;
Steve
Molly
Hannah
null
null
15000
0
0
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