DB2 and SQL

sunfloweremryologistData Management

Oct 31, 2013 (3 years and 9 months ago)

335 views

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

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

PHTPRM

11M987654

Wallace United Methodist

2800


DB2/SQL Functions:


Aggregate Functions cont.
:




MIN

select
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

PHTPRM

43P123456

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_TOTS

M

25000

A

11000

W

9000

P

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;



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

s
elect 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.00

000000002029023

2029023.00

000000000642056

642056.00

000000000035577

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

40

Ancient Paths Church Attn Bruce Duell

37

Faith 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
150

Test Account By Bruce
150

Test Church By Bruce
150

A Missouri Test Bruce
150

Independent Test Church For Bruce
33

Test Account By Bruce
21

Test Church By Bruce
20

A 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
150

Test Account By Bruce
150

Test Church By Bruce
150

A Missouri Test Bruce
150

Independent Test Church For Bruce
33

Test Account By Bruce
21

Test Church By Bruce
20

A 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 Te

Test Account By Bruce

Account B

Test Church By Bruce

Church By

A Missouri Test Bruce
ssouri Tes

Steve

null

Molly

null

Hannah

15000

Steve

0

Molly

0

Hannah

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