db2 and sql - mrmugmrmug.org/archives/docs/april 2014 presentation - db2 and sql.pdf · • major...
TRANSCRIPT
DB2 and SQL
bull Terminology ndash DB2 Relational database manager fully integrated into the IBMi OS
bull Major difference between IBMi (Power Systems) DB2 and DB2 Mainframe or LUW
ndash SQL Structured Query Language (SQL) allows user to define
manipulate query and secure data
ndash DDL Data Definition Language (DDL) is the portion of SQL that
creates deletes and alters DB2 database objects
bull Objects include schemas tables indexes views sequences aliases triggers procedures
ndash DML Data Manipulation Language (DML) is the portion of SQL that
manipulates or controls data
bull Select Insert Update Delete
ndash SQL vs Traditional Terminology SQL term Traditional file access term
Schema A group of related objects that consists of a
library a journal a journal receiver an SQL catalog
and an optional data dictionary A schema enables the
user to find the objects by name Another name for a
schema is collection
Library A group of related objects that enables the user
to find the objects by name
Table A set of columns and rows Physical file A set of records
Row The horizontal part of a table containing a serial
set of columns
Record A set of fields
Column The vertical part of a table of one data type Field One of more bytes of related information of one
data type
View A subset of columns and rows of one or more
tables
Logical file A subset of fields or records of up to 32
physical files
Index A collection of data in the columns of a table
logically arranged in ascending or descending order
Index A type of logical file
Package An object that contains control structures for
SQL statements to be used by an application server
SQL package An object that contains control structures
for SQL statements to be used by an application server
Catalog A set of tables and views that contain
information about tables packages views indexes
and constraints
No similar object However the Display File Description
(DSPFD) and Display File Field Description (DSPFFD)
commands provide some of the same information that
querying an SQL catalog provides
Source IBM i Information Center ndash V6R1
Select Statement - Syntax SELECT column names
FROM table or view name
WHERE search condition
GROUP BY column names
HAVING search condition
ORDER BY column-name
- Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
order by 1
- Results -100 Claim
-80 MedProvidr
-60 Attorney
-50 Adjuster
-40 Misc
-20 Draft
0
35 Credit
Select Statement - Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
having sum(CTPMTAMT) lt -50
order by 1
- Results
-100 Claim
-80 MedProvidr
-60 Attorney
bull RPG Equivalent to previous sql statement
d datetime ds
d ctctime s 26A
d year s 4s 0
d month s 2s 0
d tctpmtamt s 10 2 init(0)
reade (year month type) crtrnlog
dou eof crtrnlog
tctpmamnt = tctpmamnt + ctpmtamt
reade (year month type) ctrnlog
enddo
Select Statement cont - Table Joins
- Inner Join
- Returns all rows that have matching values on the join columns
- Left Outer Join
- Returns all rows an inner join would plus all rows from left table that do not have a match from
the right table
- Right Outer Join
- Returns all rows an inner join would plus all rows from right table that do not have a match from
the Left table
- Full Join
- Returns all rows from both tables that have matching values on the join columns plus all non-
matching rows from both tables
- Exception Join
- Returns only the rows from the left table (or right table) that do not have a match on the join
columns from the right table (or left table)
Select Statement cont - Unions
- Definition
- Take the results of 2 or more sub-selects and combine into 1 full select
- Example
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from bmsqlerr
where SQ_ADDTIME gt current timestamp - 24 hours
union all
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from sqlerrors
where SQ_ADDTIME gt current timestamp - 24 hours
order by 1 2 3
Note Union All is used to keep duplicates from all sub-selects Specifying Union without the ldquoallrdquo keyword will
automatically remove duplicates from the final result set
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
bull Terminology ndash DB2 Relational database manager fully integrated into the IBMi OS
bull Major difference between IBMi (Power Systems) DB2 and DB2 Mainframe or LUW
ndash SQL Structured Query Language (SQL) allows user to define
manipulate query and secure data
ndash DDL Data Definition Language (DDL) is the portion of SQL that
creates deletes and alters DB2 database objects
bull Objects include schemas tables indexes views sequences aliases triggers procedures
ndash DML Data Manipulation Language (DML) is the portion of SQL that
manipulates or controls data
bull Select Insert Update Delete
ndash SQL vs Traditional Terminology SQL term Traditional file access term
Schema A group of related objects that consists of a
library a journal a journal receiver an SQL catalog
and an optional data dictionary A schema enables the
user to find the objects by name Another name for a
schema is collection
Library A group of related objects that enables the user
to find the objects by name
Table A set of columns and rows Physical file A set of records
Row The horizontal part of a table containing a serial
set of columns
Record A set of fields
Column The vertical part of a table of one data type Field One of more bytes of related information of one
data type
View A subset of columns and rows of one or more
tables
Logical file A subset of fields or records of up to 32
physical files
Index A collection of data in the columns of a table
logically arranged in ascending or descending order
Index A type of logical file
Package An object that contains control structures for
SQL statements to be used by an application server
SQL package An object that contains control structures
for SQL statements to be used by an application server
Catalog A set of tables and views that contain
information about tables packages views indexes
and constraints
No similar object However the Display File Description
(DSPFD) and Display File Field Description (DSPFFD)
commands provide some of the same information that
querying an SQL catalog provides
Source IBM i Information Center ndash V6R1
Select Statement - Syntax SELECT column names
FROM table or view name
WHERE search condition
GROUP BY column names
HAVING search condition
ORDER BY column-name
- Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
order by 1
- Results -100 Claim
-80 MedProvidr
-60 Attorney
-50 Adjuster
-40 Misc
-20 Draft
0
35 Credit
Select Statement - Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
having sum(CTPMTAMT) lt -50
order by 1
- Results
-100 Claim
-80 MedProvidr
-60 Attorney
bull RPG Equivalent to previous sql statement
d datetime ds
d ctctime s 26A
d year s 4s 0
d month s 2s 0
d tctpmtamt s 10 2 init(0)
reade (year month type) crtrnlog
dou eof crtrnlog
tctpmamnt = tctpmamnt + ctpmtamt
reade (year month type) ctrnlog
enddo
Select Statement cont - Table Joins
- Inner Join
- Returns all rows that have matching values on the join columns
- Left Outer Join
- Returns all rows an inner join would plus all rows from left table that do not have a match from
the right table
- Right Outer Join
- Returns all rows an inner join would plus all rows from right table that do not have a match from
the Left table
- Full Join
- Returns all rows from both tables that have matching values on the join columns plus all non-
matching rows from both tables
- Exception Join
- Returns only the rows from the left table (or right table) that do not have a match on the join
columns from the right table (or left table)
Select Statement cont - Unions
- Definition
- Take the results of 2 or more sub-selects and combine into 1 full select
- Example
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from bmsqlerr
where SQ_ADDTIME gt current timestamp - 24 hours
union all
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from sqlerrors
where SQ_ADDTIME gt current timestamp - 24 hours
order by 1 2 3
Note Union All is used to keep duplicates from all sub-selects Specifying Union without the ldquoallrdquo keyword will
automatically remove duplicates from the final result set
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
ndash SQL vs Traditional Terminology SQL term Traditional file access term
Schema A group of related objects that consists of a
library a journal a journal receiver an SQL catalog
and an optional data dictionary A schema enables the
user to find the objects by name Another name for a
schema is collection
Library A group of related objects that enables the user
to find the objects by name
Table A set of columns and rows Physical file A set of records
Row The horizontal part of a table containing a serial
set of columns
Record A set of fields
Column The vertical part of a table of one data type Field One of more bytes of related information of one
data type
View A subset of columns and rows of one or more
tables
Logical file A subset of fields or records of up to 32
physical files
Index A collection of data in the columns of a table
logically arranged in ascending or descending order
Index A type of logical file
Package An object that contains control structures for
SQL statements to be used by an application server
SQL package An object that contains control structures
for SQL statements to be used by an application server
Catalog A set of tables and views that contain
information about tables packages views indexes
and constraints
No similar object However the Display File Description
(DSPFD) and Display File Field Description (DSPFFD)
commands provide some of the same information that
querying an SQL catalog provides
Source IBM i Information Center ndash V6R1
Select Statement - Syntax SELECT column names
FROM table or view name
WHERE search condition
GROUP BY column names
HAVING search condition
ORDER BY column-name
- Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
order by 1
- Results -100 Claim
-80 MedProvidr
-60 Attorney
-50 Adjuster
-40 Misc
-20 Draft
0
35 Credit
Select Statement - Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
having sum(CTPMTAMT) lt -50
order by 1
- Results
-100 Claim
-80 MedProvidr
-60 Attorney
bull RPG Equivalent to previous sql statement
d datetime ds
d ctctime s 26A
d year s 4s 0
d month s 2s 0
d tctpmtamt s 10 2 init(0)
reade (year month type) crtrnlog
dou eof crtrnlog
tctpmamnt = tctpmamnt + ctpmtamt
reade (year month type) ctrnlog
enddo
Select Statement cont - Table Joins
- Inner Join
- Returns all rows that have matching values on the join columns
- Left Outer Join
- Returns all rows an inner join would plus all rows from left table that do not have a match from
the right table
- Right Outer Join
- Returns all rows an inner join would plus all rows from right table that do not have a match from
the Left table
- Full Join
- Returns all rows from both tables that have matching values on the join columns plus all non-
matching rows from both tables
- Exception Join
- Returns only the rows from the left table (or right table) that do not have a match on the join
columns from the right table (or left table)
Select Statement cont - Unions
- Definition
- Take the results of 2 or more sub-selects and combine into 1 full select
- Example
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from bmsqlerr
where SQ_ADDTIME gt current timestamp - 24 hours
union all
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from sqlerrors
where SQ_ADDTIME gt current timestamp - 24 hours
order by 1 2 3
Note Union All is used to keep duplicates from all sub-selects Specifying Union without the ldquoallrdquo keyword will
automatically remove duplicates from the final result set
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Select Statement - Syntax SELECT column names
FROM table or view name
WHERE search condition
GROUP BY column names
HAVING search condition
ORDER BY column-name
- Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
order by 1
- Results -100 Claim
-80 MedProvidr
-60 Attorney
-50 Adjuster
-40 Misc
-20 Draft
0
35 Credit
Select Statement - Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
having sum(CTPMTAMT) lt -50
order by 1
- Results
-100 Claim
-80 MedProvidr
-60 Attorney
bull RPG Equivalent to previous sql statement
d datetime ds
d ctctime s 26A
d year s 4s 0
d month s 2s 0
d tctpmtamt s 10 2 init(0)
reade (year month type) crtrnlog
dou eof crtrnlog
tctpmamnt = tctpmamnt + ctpmtamt
reade (year month type) ctrnlog
enddo
Select Statement cont - Table Joins
- Inner Join
- Returns all rows that have matching values on the join columns
- Left Outer Join
- Returns all rows an inner join would plus all rows from left table that do not have a match from
the right table
- Right Outer Join
- Returns all rows an inner join would plus all rows from right table that do not have a match from
the Left table
- Full Join
- Returns all rows from both tables that have matching values on the join columns plus all non-
matching rows from both tables
- Exception Join
- Returns only the rows from the left table (or right table) that do not have a match on the join
columns from the right table (or left table)
Select Statement cont - Unions
- Definition
- Take the results of 2 or more sub-selects and combine into 1 full select
- Example
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from bmsqlerr
where SQ_ADDTIME gt current timestamp - 24 hours
union all
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from sqlerrors
where SQ_ADDTIME gt current timestamp - 24 hours
order by 1 2 3
Note Union All is used to keep duplicates from all sub-selects Specifying Union without the ldquoallrdquo keyword will
automatically remove duplicates from the final result set
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Select Statement - Example select sum(CTPMTAMT) ctpmttyp
from DLYFILECRTRNLOG
where year(ctctime)= 2011
and month(ctctime) = 10
group by ctpmttyp
having sum(CTPMTAMT) lt -50
order by 1
- Results
-100 Claim
-80 MedProvidr
-60 Attorney
bull RPG Equivalent to previous sql statement
d datetime ds
d ctctime s 26A
d year s 4s 0
d month s 2s 0
d tctpmtamt s 10 2 init(0)
reade (year month type) crtrnlog
dou eof crtrnlog
tctpmamnt = tctpmamnt + ctpmtamt
reade (year month type) ctrnlog
enddo
Select Statement cont - Table Joins
- Inner Join
- Returns all rows that have matching values on the join columns
- Left Outer Join
- Returns all rows an inner join would plus all rows from left table that do not have a match from
the right table
- Right Outer Join
- Returns all rows an inner join would plus all rows from right table that do not have a match from
the Left table
- Full Join
- Returns all rows from both tables that have matching values on the join columns plus all non-
matching rows from both tables
- Exception Join
- Returns only the rows from the left table (or right table) that do not have a match on the join
columns from the right table (or left table)
Select Statement cont - Unions
- Definition
- Take the results of 2 or more sub-selects and combine into 1 full select
- Example
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from bmsqlerr
where SQ_ADDTIME gt current timestamp - 24 hours
union all
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from sqlerrors
where SQ_ADDTIME gt current timestamp - 24 hours
order by 1 2 3
Note Union All is used to keep duplicates from all sub-selects Specifying Union without the ldquoallrdquo keyword will
automatically remove duplicates from the final result set
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
bull RPG Equivalent to previous sql statement
d datetime ds
d ctctime s 26A
d year s 4s 0
d month s 2s 0
d tctpmtamt s 10 2 init(0)
reade (year month type) crtrnlog
dou eof crtrnlog
tctpmamnt = tctpmamnt + ctpmtamt
reade (year month type) ctrnlog
enddo
Select Statement cont - Table Joins
- Inner Join
- Returns all rows that have matching values on the join columns
- Left Outer Join
- Returns all rows an inner join would plus all rows from left table that do not have a match from
the right table
- Right Outer Join
- Returns all rows an inner join would plus all rows from right table that do not have a match from
the Left table
- Full Join
- Returns all rows from both tables that have matching values on the join columns plus all non-
matching rows from both tables
- Exception Join
- Returns only the rows from the left table (or right table) that do not have a match on the join
columns from the right table (or left table)
Select Statement cont - Unions
- Definition
- Take the results of 2 or more sub-selects and combine into 1 full select
- Example
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from bmsqlerr
where SQ_ADDTIME gt current timestamp - 24 hours
union all
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from sqlerrors
where SQ_ADDTIME gt current timestamp - 24 hours
order by 1 2 3
Note Union All is used to keep duplicates from all sub-selects Specifying Union without the ldquoallrdquo keyword will
automatically remove duplicates from the final result set
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Select Statement cont - Table Joins
- Inner Join
- Returns all rows that have matching values on the join columns
- Left Outer Join
- Returns all rows an inner join would plus all rows from left table that do not have a match from
the right table
- Right Outer Join
- Returns all rows an inner join would plus all rows from right table that do not have a match from
the Left table
- Full Join
- Returns all rows from both tables that have matching values on the join columns plus all non-
matching rows from both tables
- Exception Join
- Returns only the rows from the left table (or right table) that do not have a match on the join
columns from the right table (or left table)
Select Statement cont - Unions
- Definition
- Take the results of 2 or more sub-selects and combine into 1 full select
- Example
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from bmsqlerr
where SQ_ADDTIME gt current timestamp - 24 hours
union all
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from sqlerrors
where SQ_ADDTIME gt current timestamp - 24 hours
order by 1 2 3
Note Union All is used to keep duplicates from all sub-selects Specifying Union without the ldquoallrdquo keyword will
automatically remove duplicates from the final result set
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Select Statement cont - Unions
- Definition
- Take the results of 2 or more sub-selects and combine into 1 full select
- Example
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from bmsqlerr
where SQ_ADDTIME gt current timestamp - 24 hours
union all
select sq_addtime sq_errcode
sq_program left(sq_statmnt110)
from sqlerrors
where SQ_ADDTIME gt current timestamp - 24 hours
order by 1 2 3
Note Union All is used to keep duplicates from all sub-selects Specifying Union without the ldquoallrdquo keyword will
automatically remove duplicates from the final result set
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Insert Statement Syntax INSERT INTO table-name
(column1 column2 )
VALUES (value-for-column1 value-for-column2 )
INSERT INTO table-name
(column1 column2 )
Select columna columnb hellip from Table_NameB
Example INSERT INTO STEVEW_CMSCMSQRYPRD2
(CQ_CLAIM CQ_ENTNBRCQ_HICNCQ_RCVDTE CQ_LNAME
CQ_FINITCQ_CMSDOBCQ_GENDERCQ_CMSSSN)
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM DLYFILECMSQRYIN A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
and cxrprirole=Claimant
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Insert Statement ndash Join Explanation
Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
- CQ_CLAIM_ - defined as 30A
- XRCLM - defined as 70 S
- In order to join CMSQRYIN1 to CRENTXREF
- remove trailing blanks remove xrsquo7drsquo from beginning and end and convert to DEC(70)
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Insert Statement ndash Join Explanation Select cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70)) as CQ_CLAIM_
denentstrip(strip(aCQ_HICN_T )Bx7d) as CQ_HICN_
aCQ_RCVDTE_ aCQ_LNAME_ aCQ_FINIT_ aCQ_CMSDOB_ aCQ_GENDER_
aCQ_CMSSSN_
FROM STEVEW_CMSCMSQRYIN1 A
join DLYFILECRENTXREF C on cast(strip(strip(aCQ_CLAIM_T )Bx7d) as dec(70))=XRCLM
join DLYFILECRENTITY D on cxrent=denent
and upper(ACQ_LNAME_)=upper(substr(Dennaml16))
and right(Acq_cmsssn_4)=right(Denssn4)
- CQ_LNAME_ - defined as 6A and stored in all lower case
- ENNAML - defined as 30A and stored in mixed case
- CQ_CMSSSN_ - defined as 11A
- ENSSN - defined as 90 S
- In order to join to CRENTITY
- convert cq_lname_ and ennaml to upper case and select only the first 6 bytes from ennaml
- take the 4 right digits from both CQ_CMSSSN_ and ENSSN
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Update Statement Syntax UPDATE table-name
SET column-1 = value-1 column-2 = value-2
WHERE search-condition
UPDATE table-name TBL1
SET column-1 = (Select column-a from table-nameB TBL2
where TBL1Column-3=TBL2Column-3)
WHERE search-condition
Example update STEVEW_CMSCMSQRYEXP1 a
set aCQ_ADJNAME=
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
where exists
(
select strip(bhefnamT )|| ||bhelnam from
dlyfilecrhoemp b where aCQ_ADJ=bhesubid
)
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Delete Statement Syntax DELETE FROM table-name
WHERE search-condition
Example
delete from mscsqlciinfi
where ciid=708865
and ciinfid in (191898341918983519189836)
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Embedded SQL
- Dynamic SQL
- Allows application to define and run SQL within the program
- Prepared at program run time
- May have more overhead than Static SQL
- Static SQL
- Prepared at precompile
- May be more efficient than dynamic
- No Prepare
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Static Embedded SQL ndash Example exec sql declare c1Count cursor for
SELECT count()
FROM pgmdevlog
WHERE pgname = odobnm and
pgcy = odcy and pgyy = odyy and
pgmm = odmm and pgdd = oddd and
pgcode = IN
exec sql open c1Count
exec sql fetch c1Count into count
select
when sqlcod = 100 No SQL entry
noLogEntry = on
when sqlcod = zeros SQL entry found
if count gt zeros
noLogEntry = off
else
noLogEntry = on
endif
other SQL error of some kind
noLogEntry = on
endsl
exec sql close c1count
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Dynamic Embedded SQL ndash Example sqlprof = SELECT dvsid +
FROM dvs +
inner join pg on pgpgid = dvspgid +
WHERE upper(poltypcde) = + PolicyType (CMP)
and upper(pgdspdsc) = + Navigation (GENERAL)
and upper(dvsdspdsc) = + Pagebar (INSURED)
FOR READ ONLY
exec sql prepare sprof from sqlprof
exec sql declare cprof cursor for sprof
exec sql open cprof using PolicyType Navigation Pagebar
exec sql fetch cprof into dvsid
if sqlcod lt 0
exec sql close cprof
WriteSQLERR(lsquoGetSectionsrsquosqlcodsqlstatesqlprof)
return sqlerror
else
exec sql close cprof
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Embedded SQL ndash SQL Error Handling
- Previous Method
- Hundreds of programs ndash Each Program writes to file when SQLCODE lt 0
- Need For SQLSTATE
SQLState Description SQL Code
- Updated Method
- Service Program written and called when SQL Error occurs
- SQLState Added to new file
22001 Character data right truncation occurred for example an update or insert value -302 -303 -404
is a string that is too long for the column or a datetime value cannot be assigned -433 -802
to a variable because it is too small
22003 A numeric value is out of range -302 -304 -406
-446 -802
22023 A parameter or variable value is invalid -302 -304 -406
-802
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
bull DB2 Catalog ndash DB2 ldquometadatardquo ndash provides information about the DB2 environment
bull SYSTABLES ndash Information for every table alias or view in SQL schema
bull SYSTABLESTAT ndash Statistical information for every table in SQL schema
bull SYSINDEXES ndash Information on every index in SQL schema
bull SYSKEYS ndash Information on every column of an SQL Index
bull Link to all DB2 catalog information ndash httppublibboulderibmcominfocenterIBMiv6r1m0topicdb2rbafzcatalogtblshtmresultof=227379737461
626c65732220227379737461626c2220
bull Practical Example
ndash Goal Need to quickly determine if any of the AC2 history tables have any rows at all where the effective
timestamp column is not null
ndash Approach
raquo select select count() from ||strip(system_table_schema)||||table_name|| where effective_timestamp is
not null
from qsys2systables where system_table_schema=PRDSQL
and table_name like _HIST
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
bull System i Navigator ndash Manage DB2 Objects
ndash Monitor Active Jobs
ndash Peruse Output Queues
ndash Access Files On The Integrated File System
ndash Backup The System
ndash Attend To Authorization Lists
ndash Assess And Monitor DB2 Performance
bull Key feature ndash Other versions of DB2 donrsquot have this and organizations purchase outside
vendor tools
ndash Run SQL Scripts And Save Them For Future Usage
ndash And probably much more
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
bull IBMi Navigator Performance Related Features (not an all-
inclusive list)
ndash Index Advisor
bull Determines if creation of permanent index will help query performance
bull Condensed Index Advisor
bull Can also be accessed from the Visual Explain interface (will cover briefly later)
ndash SQL Plan Cache
bull Information about SQE queries in the database
bull Actively changes and plans can be removed from the cache
bull Plan Cache Snapshot can be taken ndash information contained in files
ndash Visual Explain
bull Graphical representation of the access paths DB2 will use to satisfy the query
bull Select Insert Update Delete all supported
bull Ability to highlight expensive operations
bull Ability to invoke Statistics or Index Advisor from Visual Explain
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Index Advisor
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
SQL Plan Cache - Monitor Plan Cache size and summary SQL activity
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
SQL Plan Cache
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
SQL Plan Cache
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
SQL Plan Cache Notes - Plan Cache Automatically Flushed After IPL
- Plan Cache Size
- Dynamically adjusted from properties window
- Ramifications of making the plan cache too large
- After IPL - adjusted to 512 mb default
- PTF - MF52486
- Application of PTF will preserve size after adjusting the size once more
- Call qsys2change_plan_cache_size(3072)
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
SQL Plan Cache Snapshots
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
SQL Plan Cache Snapshots (Table Scan Summary)
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Program To Create Plan Cache Snapshot
h INDENT(| ) OPTION(NODEBUGIOSRCSTMT)
d CURRENTDATE S D
d SNAPSHOTNAME S 10A
FREE
CURRENTDATE = DATE()
SNAPSHOTNAME = PC +
CHAR ( SUBDT(CURRENTDATEYEARS)) +
CHAR ( SUBDT(CURRENTDATEMONTHS)) +
CHAR ( SUBDT(CURRENTDATEDAYS))
EXEC SQL
CALL QSYS2DUMP_PLAN_CACHE(SWALLACE SNAPSHOTNAME)
DID AN ERROR OCCUR
IF SUBST(SQLSTATE12)ltgt 00
DO SOMETHINGLIKE SEND A MESSAGE TO QSYSOPR
ENDIF
RETURN
END-FREE
Note Program runs through the IBMi Job Scheduler every Friday night at 2345
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Visual Explain
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Visual Explain
Visual Explain
Visual Explain
Visual Explain
The End
Visual Explain
Visual Explain
Visual Explain
The End
Visual Explain
Visual Explain
The End
Visual Explain
The End
The End