My Favorite Db2 SQL Enhancements for
Db2 V9-V12
Presented by Tony Andrews, Themis Inc.
Twitter:
Questions?
• I will try my best to get to some questions
towards the end of the webinar.
• You can submit questions by typing into the
questions area of your webinar control panel.
• Any questions not answered due to time
constraints can be answered afterward via an
email.
Agenda - Objectives
• Highlight the many new SQL enhancements and features
from V9 to V12 that I found very useful for developers and
applications
• Many of which should be incorporated into SQL and
Applications standards.
• Take away many great SQL examples
• Empower those in the development areas
Following are some of the many application features from Db2 V8:
1) More Stage 1 predicates
2) Multi Row Fetch, Update, and Insert
3) Multiple Distincts
4) Expressions in the ‘Group By’
5) Common Table Expression
6) Dynamic Scrollable Cursors
7) Sequences versus Identity Columns
8) Materialized Query Tables (MQTs)
9) Recursive SQL
10) More efficient use of indexes. Forward and Backward scans
11) New XML functions and datatypes
12) New ‘Get Diagnostics’ for warning and error information
13) Select from an Insert statement
14) Scalar Fullselect within a ‘Select’, ‘Case’, Update, etc.
Following are some of the many application features in Db2 V9:
1) Set operations ‘Intersect’ and ‘Except’
2) Merge statement for ‘Upsert’ processing. Insert or Update
3) OLAP features for Ranking and Numbering of data
4) Native SQL Stored Procedures
5) ‘Instead of’ Triggers
6) New support and SQL for XML data
7) IBM Data Studio
8) Distinct sort avoidance with non unique indexes
9) Indexing on Expressions
10) Statistics on Views
11) Skipped locked data
12) Truncate statement
Following are some of the many application features in Db2 V9:
13) Optimizer Changes
14) Timestamp auto update for inserts and Updates
15) Optimistic locking
16) New DECFLOAT datatype
17) Select from Update or Delete getting old or new values
18) Fetch First, Order BY within subqueries
19) REOPT AUTO (Dynamic SQL)
20) Data Studio for Native Stored Procedures
Following are some of the many application features in Db2 V10:
1) Ranking, Moving Sum and AVG
2) Variable Inserts and Updates
3) Extender indicator values
4) Increased timestamp precision
5) Currently committed
6) Hash access design
7) SQL PL enhancements
8) XML Enhancements
9) Column Masking
10) Temporal Tables
11) Many Optimization improvements
12) Index included columns
13) Etc… Etc…. Etc ….
Following are some of the many application features in Db2 V11:
1) Global Variables
2) Transparent Archiving
3) SQL Grouping Sets and Rollups
4) SQL PL variable arrays
5) More SQL PL enhancements
6) More XML enhancements
7) Global Temp Table enhancements
8) New optimization features and improvements
9) Explain table additions
10) Etc… Etc…. Etc ….
Following are some of the many application features in Db2 V12:
1) Additional support for triggers
2) Pagination improvements
3) Additional support for arrays
4) MERGE improvements
5) Piece-wise deletes
6) Optimization improvements
7) Percentile functions
8) LISTAGG Function
9) RESTful services
10) Etc… Etc…. Etc ….
MERGE INTO ITEM I
USING (VALUES (1, 'WIDGIT') )
AS NEWITEM (ITEMNO, ITEMNAME)
ON I.ITEMNO = NEWITEM.ITEMNO
WHEN MATCHED THEN
UPDATE SET ITEMNAME = NEWITEM.ITEMNAME
WHEN NOT MATCHED THEN
INSERT (ITEMNO,ITEMNAME)
VALUES (NEWITEM.ITEMNO, NEWITEM.ITEMNAME);
Set of values for a row
Establishes a match
for an existing row
Update action if row
is already present
Insert action if no row is found
V9 MERGE Statement
MERGE INTO EMP E1
USING (SELECT EMPNO, SALARY
FROM EMP
WHERE DEPTNO = 'A00') AS E2
ON (E1.EMPNO = E2.EMPNO)
WHEN MATCHED AND E1.SALARY >= 50000 THEN
UPDATE SET E1.SALARY = E1.SALARY * 500
WHEN MATCHED AND E1.SALARY < 40000 THEN
DELETE
WHEN MATCHED AND E1.SALARY < 50000 THEN
UPDATE SET E1.SALARY = E1.SALARY * 1.1
ELSE
IGNORE
V12 MERGE Enhancements
Allows for a Select to specify input data
Allows for multiple conditions on WHEN
Allows for delete option
MERGE INTO EMPPROJACT EPA
USING (SELECT EMPNO, SALARY
FROM EMP
WHERE DEPTNO = 'A00') AS E
ON (EPA.EMPNO = E.EMPNO)
WHEN MATCHED AND E.SALARY >= 50000 THEN
UPDATE SET EPA.EMPTIME = 99
WHEN MATCHED AND E.SALARY < 40000 THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (EMPNO, PROJNO, ACTNO)
VALUES (E.EMPNO, 'P99999', '123')
V12 MERGE Enhancement
Allows for source data to be different
Allows for an update or delete to affect
more than 1 row
V9 Final Table: Great for Auto Key Values
INSERT INTO CUSTOMER
(CUSTNO, CUST_NAME, LOCATION)
VALUES
(NEXT VALUE FOR CUSTKEY,
'Themis Inc.', 'NJ')
SELECT CUSTNO FROM FINAL TABLE
(INSERT INTO CUSTOMER
(CUSTNO, CUST_NAME, LOCATION)
VALUES
(NEXT VALUE FOR CUSTKEY,
'Themis Inc.', 'NJ')
)
CUSTKEY =
Sequence Object
Final Table
SQL key words
Identity Column - Review
CREATE TABLE CUSTOMER
(CUSTNO INTEGER NOT NULL
GENERATED ALWAYS
AS IDENTITY
(START WITH 200
INCREMENT BY 1
CACHE 50)
,CUST_NAME CHAR(20) NOT NULL
,LOCATION CHAR(2) )
Sequence Info will be
tied directly to CUSTNO
Column
V9 Final Table: Great for Identity Column Usage
INSERT INTO CUSTOMER
(CUST_NAME,
LOCATION)
VALUES
('Themis Inc.', 'NJ')
SELECT CUSTNO FROM FINAL TABLE
(INSERT INTO CUSTOMER
(CUST_NAME, LOCATION)
VALUES
('Themis Inc.', 'NJ') )
No mention of
CUSTNO Value
is supplied by
Db2
SELECT ITEMNAME, UPD_IND FROM FINAL TABLE
(MERGE INTO ITEM I
INCLUDE (UPD_IND CHAR(1))
USING (VALUES (1, 'SOCKET') )
AS NEWITEM (ITEMNO, ITEMNAME)
ON I.ITEMNO = NEWITEM.ITEMNO
WHEN MATCHED THEN
UPDATE SET ITEMNAME = NEWITEM.ITEMNAME,
UPD_IND = 'U'
WHEN NOT MATCHED THEN
INSERT (ITEMNO,ITEMNAME,UPD_IND)
VALUES (NEWITEM.ITEMNO,
NEWITEM.ITEMNAME,'I') )
V9 SELECT from MERGE: To get ‘I’ or ‘U’ back
ITEMNAME UPD_IND
SOCKET U
V9 Old Table: Retrieve values before the action
SELECT EMPNO, LASTNAME
FROM OLD TABLE
(DELETE FROM EMP
WHERE DEPTNO = ‘D11’
)
Old Table
SQL key words
V9 New Uses for ORDER BY / FETCH FIRST
SELECT SEMP.LASTNAME,SEMP.FIRSTNME,D.DEPTNAME
FROM (SELECT LASTNAME, FIRSTNME, DEPTNO
FROM EMP
ORDER BY DEPTNO
FETCH FIRST xx ROWS ONLY) AS SEMP
JOIN DEPT D ON SEMP.DEPTNO = D.DEPTNO
V9 Intersect / Intersect ALL
SELECT #1 SELECT #2
Abbot Baker
Jones Jones
Smith Jones
Smith Smith
Smith Smith
Jones
Smith
INTERSECT
Jones
Smith
Smith
INTERSECT ALL
X
X
V9 Except / Except ALL
SELECT #1 SELECT #2
Abbot Baker
Jones Jones
Smith Jones
Smith Smith
Smith Smith
Abbot
EXCEPT
Abbot
Smith
EXCEPT ALL
V9 Rank Function
Usage:
SELECT LASTNAME, SALARY,
RANK() OVER (ORDER BY SALARY DESC) AS R
FROM EMP
LASTNAME SALARY R
HAAS 52750.00 1
HEMMINGER 46500.00 2
LUCCHESI 46500.00 2
THOMPSON 41250.00 4
V9 Dense Rank Function
Usage:
SELECT LASTNAME, SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS R
FROM EMP
LASTNAME SALARY R
HAAS 52750.00 1
HEMMINGER 46500.00 2
LUCCHESI 46500.00 2
THOMPSON 41250.00 3
V9 Row Numbering
Usage:
SELECT LASTNAME, SALARY,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS R
FROM EMP
LASTNAME SALARY R
HAAS 52750.00 1
HEMMINGER 46500.00 2
LUCCHESI 46500.00 3
THOMPSON 41250.00 4
SQL Percentiles It’s easy to get ranking numbers, but
how do we use those with percentiles
(top 5%, bottom 10%, etc.)?
Percentiles:
1 You need to know total number in data
2 You need to know your rank within
Example:
1 There are 600 students in your class
2 You are ranked 120 based on GPA
3 (120/600)*100=20, AND 100-20=80
4 80th percentile = you are in top 20%
Example 1: EMP table contains 33 employees.
Retrieve those emps making top 5% of salaries.
--
-- THIS QUERY CALCULATES PERCENTILE IN THE CTE SO WE CAN THEN
-- ADD 'WHERE' LOGIC ON IT.
--
WITH TEMP_RANK AS
(SELECT EMPNO, LASTNAME, SALARY,
SMALLINT(DENSE_RANK () OVER (ORDER BY SALARY ASC) ) AS RANK,
(SMALLINT(DENSE_RANK () OVER (ORDER BY SALARY ASC) ) / 33.0)
* 100 AS PERCENTILE
FROM THEMIS90.EMP
ORDER BY SALARY ASC)
SELECT EMPNO, LASTNAME, SALARY, RANK,
PERCENTILE
FROM TEMP_RANK
WHERE PERCENTILE >= 95
ORDER BY PERCENTILE DESC
;
SQL Percentiles Example 1: EMP table contains 33 employees.
Retrieve those emps making top 5% of salaries.
Sampling of all data and their percentiles
V12 SQL Percentiles V12R1M500
PERCENTILE_CONT: Calculated number based n
values
PERCENTILE_DISC: Returns one of the
specific values
Used to answer questions like:
What salary is 95th percentile for a
specific DEPTNO? JOB? etc.
V9 Row Change Timestamp
CREATE TABLE EMP2
(EMPNO CHAR(06) NOT NULL
,LAST_NAME CHAR(20) NOT NULL
,FIRST_NAME CHAR(20) NOT NULL
,ZIP_CODE CHAR(5) NOT NULL
,BIRTH_DTE DATE NOT NULL
,UPD_TSP TIMESTAMP NOT NULL
GENERATED ALWAYS FOR EACH ROW
ON UPDATE AS ROW CHANGE TIMESTAMP)
…
V9 Row Changed Timestamp
Now that the ROW CHANGE TIMESTAMP is defined for a column …..
you can code logic using the column. The following will get all rows
that have been changed in the last week.
SELECT …….
FROM EMP2
WHERE UPD_TSP > CURRENT TIMESTAMP – 7 DAYS
RID Function
SELECT EMPNO, RID(EMP) AS RID, LASTNAME, SALARY
FROM EMP
WHERE EMPNO = '000030'
;
UPDATE EMP
SET SALARY = 100000.00
WHERE RID(EMP) = 8708
;
V9 Optimistic Locking in COBOL
EXEC SQL
FETCH C1
INTO :V-EMPNO,
:v-UPD_TSP,
:V-MY-RID, BIGINT
:V-UPD-TSP
END-EXEC.
...
EXEC SQL
UPDATE EMP E
SET LASTNAME = :NEW-LASTNAME
WHERE RID(E) = :MY-RID
AND UPD_TDP = :V-UPD-TSP
END-EXEC.
V12 USERID Auto Generated
CREATE TABLE EMP2
(EMPNO CHAR(06) NOT NULL
,LAST_NAME CHAR(20) NOT NULL
,FIRST_NAME CHAR(20) NOT NULL
,ZIP_CODE CHAR(5) NOT NULL
,BIRTH_DTE DATE NOT NULL
,UPD_TSP TIMESTAMP NOT NULL
GENERATED ALWAYS FOR EACH ROW
ON UPDATE AS ROW CHANGE TIMESTAMP
,USERID VARCHAR(128)
GENERATED ALWAYS AS (SESSION_USER)
)
NOTE: Must be VARCHAR(128)
Must be APPLCOMPAT( V12R1M504) OR HIGHER
V10 Moving Sum / Avg
Example 1:
SELECT EMPNO, DEPTNO, SALARY,
SUM (SALARY)
OVER (
ORDER BY SALARY ASC
ROWS UNBOUNDED PRECEDING
) AS SUM_SAL
FROM EMP
V10 Moving Sum / Avg
Example 2:
SELECT DEPTNO, EMPNO,
SALARY,
SUM (SALARY)
OVER (PARTITION BY DEPTNO
ORDER BY SALARY ASC
ROWS UNBOUNDED
PRECEDING
) AS SUM_SAL
FROM EMP
ORDER BY DEPTNO
Moving SUM and AverageAvg(Salary) Trend by Job
--
-- SHOW TRENDS OF AVG(SALARY) BY JOB BASED ON HIREDATE
--
SELECT EMPNO, JOB, SALARY, HIREDATE,
AVG (SALARY) OVER (PARTITION BY JOB
ORDER BY HIREDATE ASC
ROWS UNBOUNDED PRECEDING
) AS AVG_SAL
FROM EMP
WHERE JOB >= 'DESIGNER'
ORDER BY JOB, HIREDATE
;
Avg(Salary) Trend by Job
Note: Average Salary trending down as time goes on
Timestamp Precision Enhancements
• Sub-second precision up to 12 decimal positions
• TIMESTAMP WITH TIMEZONE
• CURRENT TIMESTAMP
Timestamp Precision Enhancements
• Storage size can vary from 7 bytes (0 decimal places) to 13
bytes (12 decimal places)
• Casting always returns a precision of 6
SELECT CAST('2012-01-23-04.30.42.123456789‘ AS TIMESTAMP)
AS TMS_CAST
FROM SYSIBM.SYSDUMMY1
Returns: '2012-01-23-04.30.42.123456'
Current Timestamp Special Register
SELECT CURRENT TIMESTAMP AS CUR_TMS1,
CURRENT TIMESTAMP(12) AS CUR_TMS12,
CURRENT TIMEZONE AS CUR_TMZ,
CURRENT TIMESTAMP WITH TIMEZONE AS CUR_TMS3
FROM SYSIBM.SYSDUMMY1
CUR_TMS1 CUR_TMS12
2019-01-25-14.34.51.387362 2019-01-25-14.34.51.387362453234
CUR_TMZ CUR_TMS3
-50000. 2019-01-25-14.34.51.387362-05:00
V10 Currently Committed Data
Bind Option: CONCURRENTACCESSRESOLUTION
Prepare Option: USE CURRENTLY COMMITTED
Using currently committed , only committed data is
returned, as was the case previously, but now read
processes do not wait for writers or deleters to release
locks. Instead, readers return data that is based on the
currently committed version; that is, data prior to the start
of the write or delete operation.
What happens when a delete lock in encountered? Reader will still get that row.
What happens when an insert lock is encountered? Reader will not get that row.
V10 XML Enhancements
• XML Everywhere including
– Variables
– Scalar and Table Functions
– SQL Procedures
• XML Schema Validation
• Node Level XML Updates
• Binary XML
• Concurrency Control (Versioning)
• XML Date and Time Data Types
• Enhanced XML Index Support
<patient id="13579">
<name>John Doe</name>
<addr>
<street>123 Main</street>
<city>Anytown</city>
<state>PA</state>
<zip>19134</zip>
</addr>
<phone type="home">610-654-1234</phone>
<phone type="work">610-987-4321</phone>
<email>[email protected]</email>
<service>
<sdate>2008-09-22</sdate>
<reason>flu shot</reason>
<descrip>gave flu shot</descrip>
<cost>40.00</cost>
<copay>10.00</copay>
</service>
<service>
<sdate>2008-05-11</sdate>
<reason>sore foot</reason>
<descrip>referred to specialist</descrip>
<cost>60.00</cost>
<copay>10.00</copay>
</service>
</patient>
Nodes
Well-formed
Elements
Attributes
Values (text)
Valid
Root
An XML Document
XML – Why?
• Flexible
• Easy to share
• Self-describing
• Neutral for exchanging data among diverse devices
– Universal standard for data interchange
– Independent of the platform or vendor
• Growth of XML data
– XML-based industry and data standards
– XML data becoming more critical to enterprise operations
Path Expression Examples 1) SELECT XMLQUERY('/' PASSING PATIENT_XML)
FROM PATIENT;
2) SELECT XMLQUERY('//service' PASSING PATIENT_XML)
FROM PATIENT;
3) SELECT XMLQUERY('/patient/service' PASSING PATIENT_XML)
FROM PATIENT;
4) SELECT XMLQUERY('//phone[@type="home"]' PASSING PATIENT_XML
FROM PATIENT;
5) SELECT XMLQUERY('/patient/name' PASSING PATIENT_XML)
FROM PATIENT;
6) SELECT XMLQUERY('/patient/addr[state="NJ"]' PASSING PATIENT_XML)
FROM PATIENT;
• Example
SELECT *
FROM patient
WHERE XMLEXISTS('$x/patient/addr[fn:exists(zip)]'
passing patient_xml as "x")
CREATE INDEX PATIDX4 ON PATIENT (PATIENT_XML)
GENERATE KEY USING XMLPATTERN '/patient/addr/fn:exists(zip)‘
AS SQL varchar(1)
Supporting Index
SELECT *
FROM patient
WHERE XMLEXISTS('/patient[fn:upper-case(name)= "JOHN DOE"]'
passing patient_xml)
Supporting Index
CREATE INDEX PATIDX3 ON PATIENT (PATIENT_XML)
GENERATE KEY USING XMLPATTERN '/patient/name/fn:upper-case(.)'
AS SQL varchar(25)
• Example
XML Indexing
V10 Temporal Tables
Temporal: Pertaining to date and time
New table design options
Support of current and history data
New SQL query syntax
V10 Temporal Tables – System Time
47
CREATE TABLE EMP_TEMPORAL_CUR
(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(20) NOT NULL,
MIDINIT CHAR(01) NOT NULL,
LASTNAME VARCHAR(20) NOT NULL,
DEPTNO CHAR(3) NOT NULL,
SALARY DEC(9,2) NOT NULL,
BONUS DEC(9,2) NOT NULL,
COMM DEC(9,2) NOT NULL,
FROM_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
TO_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
TRANS_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS
TRANSACTION START ID,
PERIOD SYSTEM_TIME (FROM_TS, TO_TS)
) ;
Note: You can add a generated
timestamp and userid also
V10 Temporal Tables – System Time
CREATE TABLE EMP_TEMPORAL_HIS
(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(20) NOT NULL,
MIDINIT CHAR(01) NOT NULL,
LASTNAME VARCHAR(20) NOT NULL,
DEPTNO CHAR(3) NOT NULL,
SALARY DEC(9,2) NOT NULL,
BONUS DEC(9,2) NOT NULL,
COMM DEC(9,2) NOT NULL,
FROM_TS TIMESTAMP(12) NOT NULL,
TO_TS TIMESTAMP(12) NOT NULL,
TRANS_TS TIMESTAMP(12) NOT NULL
) ;
ALTER TABLE MP_TEMPORAL_CUR
ADD VERSIONING
USE HISTORY TABLE
EMP_TEMPORAL_HIS
Note: If a generated
timestamp and userid is defined
in main table, they get defined
only as TIMESTAMP NOT NULL,
VARCHAR(128)
V10 Temporal Tables – Queries
SELECT * FROM EMP_TEMPORAL_CUR
FOR SYSTEM_TIME AS OF '2018-01-30-13.46.12.107358109000'
WHERE EMPNO = '000010'
;
SELECT * FROM EMP_TEMPORAL_CUR
FOR SYSTEM_TIME BETWEEN '2016-01-30-13.44.12.107358109000'
AND '2016-03-30-15.46.12.107358109000'
WHERE EMPNO = '000010'
;
V10 Queryno Integer
Example 1 Example 2
------------------- -----------------
SELECT …… DELETE
FROM …… FROM …….
WHERE …… WHERE …..
ORDER BY …. QUERYNO 6
QUERYNO 5
V11 Global Variables
• Named memory variables in
Db2 that may be accessed and
modified by SQL statements
• Enable sharing of data
between different SQL
statements without an
application facilitating the data
transfer
• Values are unique to an
application scope
• Values stay persistent across
SQL commit
Global Variable Creation
CREATE VARIABLE GV_TSP TIMESTAMP
DEFAULT CURRENT TIMESTAMP;
GRANT ALL PRIVILEGES ON VARIABLE GV_TSP
TO PUBLIC;
Choose a naming
convention that identifies
this as a global variable
Global Variable Usage
EXEC SQL
SET GV_TSP = ‘2014-12-31.00.00.00.000000’
END-EXEC.
…
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT CUSTNO FROM CUSTOMER
WHERE LAST_CALL < GV_TSP
END-EXEC.
…
EXEC SQL
DECLARE C2 CURSOR FOR
SELECT ORDERNO FROM ORDER
WHERE ORDER_TSP < GV_TSP
END-EXEC.
Transparent Archiving
• Db2 10 introduced “Temporal
Tables”
• Db2 11 adds the ability to
automatically archive deleted
data as an alternative to
“System Temporal” Tables
• Like System Temporal Tables
an archive table is used to
capture deleted rows
V11 Archive Table
CREATE TABLE CUST
(CUSTNO INTEGER NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
PHONENO CHAR(12) NOT NULL,
LAST_CONTACT_DATE DATE NOT NULL,
GENDER CHAR(1),
BIRTHDATE DATE,
PRIMARY KEY(CUSTNO) )
IN THEMISDB.TSCUST
No timestamps
needed
V11 Archive Table Usage
CREATE TABLE CUST_ARCHIVE
LIKE CUST
IN THEMISDB.TSCUSTA;
ALTER TABLE CUST ENABLE ARCHIVE
USE CUST_ARCHIVE;
Connects the two
tables in an archive
relationship
V11 Global Variables for Archiving
• SYSIBMADM.MOVE_TO_ARCHIVE
• SYSIBMADM.GET_ARCHIVE
Note: Both were initially defaulted to ‘N’.
Not so in V12 and retrofitted with an
APAR in V11
zPARM MOVE_TO_ARCHIVE_DEFAULT
V11 GROUPING SETS
SELECT DEPTNO, JOB, AVG(SALARY) AS AVG
FROM EMP
WHERE DEPTNO < 'B99'
GROUP BY GROUPING SETS
( (DEPTNO, JOB) , (DEPTNO), ( ) )
DEPTNO JOB AVG
A00 CLERK 29250.00
A00 PRES 52750.00
A00 SALESREP 46500.00
A00 45312.50
B01 MANAGER 41250.00
B01 41250.00
44500.00
V11 CUBE
SELECT DEPTNO, JOB,
AVG(SALARY) AS AVG
FROM EMP
WHERE DEPTNO < 'B99'
GROUP BY CUBE (DEPTNO, JOB)
DEPTNO JOB AVG
A00 CLERK 29250.00
A00 PRES 52750.00
A00 SALESREP 46500.00
A00 45312.50
B01 MANAGER 41250.00
B01 41250.00
CLERK 29250.00
PRES 52750.00
SALESREP 46500.00
MANAGER 41250.00
44500.00
SQL PL Stored Procedure Processing
Prior to V11
CREATE PROCEDURE SP1000
(IN P_IN_DEPTNO CHAR(3),
OUT P_OUT_SQLCODE INTEGER)
P1: BEGIN
DECLARE I INTEGER DEFAULT 1;
DECLARE V_DEPTNO1 CHAR(1);
DECLARE V_DEPTNO2 CHAR(1);
DECLARE V_DEPTNO2 CHAR(1);
END P1
Calling Pgm
Insert into EMP …
Call SP1000 (HV1, HV2);
Rollback;
SP1000
P1 Begin:
Update DEPT …
End P1:
Modifications seen
by SP1000
Rollback affects
SP1000 Update also
Current Non-Autonomous Processing
V11 SQL PL Autonomous Processing
Prior to V11
CREATE PROCEDURE SP1000
(IN P_IN_DEPTNO CHAR(3),
OUT P_OUT_SQLCODE INTEGER)
P1: BEGIN
DECLARE I INTEGER DEFAULT 1;
DECLARE V_DEPTNO1 CHAR(1);
DECLARE V_DEPTNO2 CHAR(1);
DECLARE V_DEPTNO2 CHAR(1);
END P1
CREATE PROCEDURE SP1000
(IN P_IN_DEPTNO DEPTNO_LIST,
OUT P_OUT_SQLCODE INTEGER)
VERSION V1
…..
…..
LANGUAGE SQL
AUTONOMOUS -- Must be last keyword
P1: BEGIN
…..
…..
End P1;
Some current ways to delete data
Program single deletes with commits (cursor processing)
Program multi-row with commits (cursor processing)
Program multi-rows fetch and delete (cursor processing)
Utility Unloads
Utility REORG with discard logic
Mass Delete: Delete from table
Truncate table
Partition Rotation (drops a partition)
V12 Piece-wise Delete
When you have a need to delete a large amount of data from a table
Concerns: Locking, Concurrency, Restarts
Options: Batch program with frequent commits
(Single deletes, Multi Row deletes)
Db2 REORG Utility with discard option
But what if you needed to do this with a single SQL statement,
or without cursor processing ?
DELETE FROM ORDER_TBL
WHERE ORDER_DATE < ?
V12 Piece-wise Delete
Concerns: Possibility of millions of rows Too much
locking. Huge Rollback if a failure occurs
V12: Allows for the FETCH to be coded within a
delete statement. No need to declare a cursor.
DELETE FROM ORDER_TBL
WHERE ORDER_DATE < ?
FETCH FIRST 5000 ROWS ONLY;
COMMIT;
V12 Piece-wise Delete
CREATE PROCEDURE SPDELETE
(IN P_TABLE CHAR(128)
,IN P_LOGIC VARCHAR(500)
,IN P_FETCH_CNT SMALLINT
,OUT P_SQL_TEXT VARCHAR(1000)
,OUT P_TOT_COUNT INTEGER
,OUT P_MESSAGE_TEXT VARCHAR(1000)
,OUT P_RETCODE INTEGER
)
-- SP Options
CALLED ON NULL INPUT
RESULT SETS 0
-- COMMIT ON RETURN NO ==> Commit on Return cannot be
-- coded with AUTONOMOUS
-- You get a -628 on deploy
-- Bind Parameters
DEGREE ANY
ISOLATION LEVEL CS
VALIDATE BIND
QUALIFIER ODYTA
PACKAGE OWNER ODYTA
ASUTIME LIMIT 50000
AUTONOMOUS -- Coded Last
V12 Piece-wise SQL PL Code Dynamic SQL Delete Logic
V12 Piece-wise SQL PL Code Dynamic SQL Delete Logic
--
-- Build the SQL Delete using input parameters
--
set v_sql_delete =
'DELETE FROM '|| P_table ||
P_logic ||
' FETCH FIRST ‘ ||
P_fetch_cnt ||
' ROWS ONLY'
;
set p_sql_text = v_sql_delete;
fetch_loop: loop
execute immediate v_sql_delete;
begin
get diagnostics
v_delete_cnt = row_count;
set p_tot_count = v_delete_cnt;
end;
if v_sqlcode = +100 then
leave fetch_loop;
else
set p_tot_count = p_tot_count +
v_delete_cnt;
commit;
end if;
end loop;
PERFORM UNTIL NO-MORE-ROWS
EXEC SQL
DELETE FROM EMPPROJACT
WHERE PROJNO = 'AD3112'
FETCH FIRST :WS-NUMBER ROWS ONLY
END-EXEC
EVALUATE TRUE
WHEN SQLCODE = ZERO
DISPLAY 'SQLCODE = ZERO ON DELETE'
ADD SQLERRD(3) TO WS-TOTAL-COUNT
WHEN SQLCODE = 100
ADD SQLERRD(3) TO WS-TOTAL-COUNT
DISPLAY 'SQLCODE = 100 END OF DELETES'
DISPLAY 'TOTAL NUMBER OF DELETES = ' WS-TOTAL-
COUNT
SET NO-MORE-ROWS TO TRUE
………….
………….
V12 Piece-wise Delete COBOL
EXPLAIN(ONLY)
BIND (or REBIND)
PACKAGE…
…
EXPLAIN(ONLY)
Explain the statements
against the current
environment without
producing a new
package
Test the access path before
replacing a package.
V10 Access Path Stability
• Db2 V10: Bind/Rebind APCOMPARE
Allows the user to request a warning or failure of the bind (or rebind) if
the access path will change as a result of the bind (or rebind). No more
surprises of access path changes!
None: Db2 does not provide any warnings or errors for
access path changes
Warn: Db2 replaces the current package with any new
access paths even if a different access path is
detected. Warnings are issued.
Error: Bind fails if any of the access paths change within the
package. One SQL path change keeps the whole
package from Rebinding.
Note: Will not work for auto-rebinds due to invalidated packages.
Access Path Compare
BIND (or REBIND) PACKAGE…
…
APCOMPARE(NONE | WARN | ERROR)
Warning issued
if path changes
Bind fails if
any paths
change
APCOMPARE (ERROR)
READY
DSN SYSTEM(DB1C)
DSN
REBIND PACKAGE(THEMISCL.LOTSASQ1.(2016-04-20-00.46.36.129541))
APCOMPARE(ERROR)
DSNT285I -BC DSNTBBP2 REBIND FOR PACKAGE = DB1C.THEMISCL.LOTSASQ1,
USE OF APCOMPARE RESULTS IN:
2 STATEMENTS WHERE COMPARISON IS SUCCESSFUL
2 STATEMENTS WHERE COMPARISON IS NOT SUCCESSFUL
0 STATEMENTS WHERE COMPARISON COULD NOT BE PERFORMED.
DSNT233I -BC UNSUCCESSFUL REBIND FOR
PACKAGE = DB1C.THEMISCL.LOTSASQ1.(2016-04-20-00.46.36.129541)
DSN
END
Evaluating the Failures
• REBIND the package with EXPLAIN(ONLY)
– Generates the PLAN_TABLE entries for the “new” access paths
• Run the EXPLAIN PACKAGE statement
– Generates the PLAN_TABLE entries for the “old” access paths
• Compare and evaluate
APCOMPARE(WARN)
READY
DSN SYSTEM(DB1C)
DSN
REBIND PACKAGE(THEMISCL.LOTSASQ1.(2016-04-20-00.46.36.129541))
APCOMPARE(WARN)
DSNT285I -BC DSNTBBP2 REBIND FOR PACKAGE = DB1C.THEMISCL.LOTSASQ1,
USE OF APCOMPARE RESULTS IN:
2 STATEMENTS WHERE COMPARISON IS SUCCESSFUL
2 STATEMENTS WHERE COMPARISON IS NOT SUCCESSFUL
0 STATEMENTS WHERE COMPARISON COULD NOT BE PERFORMED.
DSNT254I -BC DSNTBRB2 REBIND OPTIONS FOR
PACKAGE = DB1C.THEMISCL.LOTSASQ1.(2016-04-20-00.46.36.129541)
...
DSNT232I -BC SUCCESSFUL REBIND FOR
PACKAGE = DB1C.THEMISCL.LOTSASQ1.(2016-04-20-00.46.36.129541)
JSON JSON is the language of the web:
1) Can be stored as JSON in Db2. Stored in SQL binary format
and can be queried much like XML. Called BSON blobs
(Binary JSON)
2) Has an SQL API with many JSON functions
3) SQL can return JSON data as normalized structured data or
return it in its JSON format.
4) Is popular for purpose of storing data easily in its native format
5) Is popular for purpose of deploying JSON schema’s easily from
application teams (reducing dependency on others in IT for
pre-defined schemas and table/object/column changes).
6) With the popularity of JavaScript, JSON is becoming the ‘Go To’
for presenting information to JavaScript clients.
Table With JSON Column CREATE TABLE EMPJSON
(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
DEPTNO CHAR(3) ,
PHONENO CHAR(4) .
HIREDATE DATE ,
JOB CHAR(8) ,
EDLEVEL SMALLINT ,
GENDER CHAR(1) ,
BIRTHDATE DATE ,
SALARY DECIMAL(9, 2) ,
BONUS DECIMAL(9, 2) ,
COMM DECIMAL(9, 2) ,
EMP_JSON_DATA VARBINARY (1500),
PRIMARY KEY(EMPNO)
;
JSON SQL Functions Db2 11
JSON2BSON: Convert JSON string into BSON format
BSON2JSON: Convert BSON BLOB into JSON string
JSON_VAL: Retrieve specific value(s) from inside a BSON blob.
Schema = SYSTOOLS
Insert Example 1 With JSON Column
INSERT INTO EMPJSON
(EMPNO, LASTNAME, FIRSTNME, MIDINIT, DEPTNO,
PHONENO, HIREDATE, JOB, EDLEVEL, GENDER, BIRTHDATE,
SALARY, BONUS, COMM, EMP_JSON_DATA)
VALUES('000001', 'ANDREWS', 'TONY', 'A', 'A00',
'1234', '2020-01-28', 'MANAGER', 16, 'M',
'1990-01-01', 50000.00, 10000.00, 0.00,
SYSTOOLS.JSON2BSON
('{NAME:"TONY ANDREWS", String
AGE:30, Numeric
ISMANAGER:TRUE, Boolean
YEARSOFSERVICE:1,
JOBS:["DEVELOPER", "TEAM MANAGER"]
}
' )
)
;
Insert Example 2 With JSON Column INSERT INTO EMPJSON
(EMPNO, LASTNAME, FIRSTNME, MIDINIT, DEPTNO,
PHONENO, HIREDATE, JOB, EDLEVEL, GENDER, BIRTHDATE,
SALARY, BONUS, COMM, EMP_JSON_DATA)
VALUES('000002', 'ANDREWS', 'TONY', 'A', 'A00',
'1234', '2020-01-28', 'MANAGER', 16, 'M',
'1990-01-01', 50000.00, 10000.00, 0.00,
SYSTOOLS.JSON2BSON
('{"EMPLOYEE": { NAME:"TONY ANDREWS", Object EMPOYEE
with attributes
AGE:30,
ISMANAGER:TRUE,
YEARSOFSERVICE:1,
JOBS:["DEVELOPER", "TEAM MANAGER"]
}
}
' )
)
;
Insert Example 3 With JSON Column INSERT INTO EMPJSON
(EMPNO, LASTNAME, FIRSTNME, MIDINIT, DEPTNO,
PHONENO, HIREDATE, JOB, EDLEVEL, GENDER, BIRTHDATE,
SALARY, BONUS, COMM, EMP_JSON_DATA)
VALUES('000003', 'ANDREWS', 'TONY', 'A', 'A00',
'1234', '2020-01-28', 'MANAGER', 16, 'M',
'1990-01-01', 50000.00, 10000.00, 0.00,
SYSTOOLS.JSON2BSON
('{"EMPLOYEE": { NAME:"TONY ANDREWS", Object EMPOYEE
AGE:30,
ISMANAGER:TRUE,
YEARSOFSERVICE:1,
JOBS:["DEVELOPER", "TEAM MANAGER"]
},
"PAY": { SALARY:50000.00, Object PAY
BONUS: 1000.00,
COMM: 0.00
}
}'
)
)
Select of JSON Column
SELECT EMPNO,
SYSTOOLS.BSON2JSON(EMP_JSON_DATA)
FROM EMPJSON
;
Update of JSON Column UPDATE EMPJSON
SET EMP_JSON_DATA =
SYSTOOLS.JSON2BSON
('{NAME:"TONY ANDREWS",
AGE:30, ISMANAGER:TRUE, YEARSOFSERVICE:1,
JOBS:["DEVELOPER", "TEAM MANAGER", "TECH LEAD"]
}
' )
;
JSON_VAL Function
SELECT JSON_VAL(EMP_JSON_DATA,'ISMANAGER','S:04') AS ISMANAGER,
JSON_VAL(EMP_JSON_DATA,'YEARSOFSERVICE','I') AS YRSOFSERV
FROM EMPJSON
WHERE EMPNO = '000001'
;
Notice this was for empno = ‘000001’. If this same query was
run against ‘000002’ it would return null due to the different
JSON data format within.
JSON_VAL Function
SELECT JSON_VAL
(EMP_JSON_DATA,'EMPLOYEE.ISMANAGER','S:04') AS ISMANAGER,
JSON_VAL
(EMP_JSON_DATA,'EMPLOYEE.YEARSOFSERVICE','I') AS YRSOFSERV
FROM EMPJSON
WHERE EMPNO = '000002'
;
{"EMPLOYEE":{"NAME":"TONY ANDREWS","AGE":30,
"ISMANAGER":true,
"YEARSOFSERVICE":1,
"JOBS":["DEVELOPER","TEAM MANAGER"]}
}
JSON_VAL Function
Other JSON Functions
JSON_LEN: Returns the number of values within an array
JSON_TYPE: Returns an element type within the document
JSON_TABLE: Returns relational output from a JSON
document
Db2’s Native REST Interface
REST is:
1) Data Server call for Create, Read, Update, Insert, Delete data
2) A single SQL statement that gets generated as a package
3) Initiated by way of a IRI (Uniform Resource Identifier) which
gets appended to a URL of a HTTP request.
4) Introduced in Db2 V12, retrofitted to V11 via APARs PI66828
and PI70477
Example: A query/package called EMP1 gets initiated
http://host-address:port#/services/THMB00CL/EMP1
How is it Invoked?
A REST service is initiated with a URI appended to
the URL of a HTTP request
http://host-address:port#/services/THMB00CL/EMP1
Post http://host-address:port#/services/THMB00CL/EMP1
Body: { “INPUTDEPTNO”: “A00” }
2: Via Db2 command BIND SERVICE in batch job IKJEFT01
You can also have the SQL statement inside JCL using the
DSNSTMT DD:
//DSNSTMT DD *
SELECT LASTNAME, FIRSTNME, HIREDATE
FROM EMP
WHERE DEPTNO = :INPUTDEPTNO
ORDER BY LASTNAME,FIRSTNME
/*
//SYSTSIN DD *
DSN SYSTEM(DC1A)
BIND SERVICE(“THMx00CL") -
NAME("EMPxx") –
………
……….
Creating a RESTful Service
Semicolon optional
Current Db2 Distributed Processing
Client
Application
SQL
Request
Direct Connect via
Type 4 driver
Db2 Connect
‘Gateway’ server
Direct Connection Connection thru Server
Java, .Net ,,,
REST Path Distributed Processing
REST Client
Application
REST
Call
Db2: Parsing,
Security checks,
Db2 for z/OS System
Post http://p390.themisinc.com
:9503/services/rest/Emp1
Body: {"INPUTDEPTNO": "A00"}
Executes package Emp1:
Returns data in JSON format
RESTful Service Output
1-91
Thank you for allowing me and Themis to share some of our experience and knowledge
today!
Tony Andrews
Twitter: @tonyandrews12
I hope that you learned something new today !!!! I am always available for consulting/contract and training assignments.
The material in this presentation is further developed in the following
Themis courses:
DB1041 – Advanced SQL FOR Db2
DB1043 – New Application Features V9-V12
SQ1010 – Dealing With Complex Queries
Cross Platform SQL
DB1037 – Advanced Query Tuning With IBM Data Studio
on z/OS
DB1032 – Db2 for z/OS Optimization Performance
and Tuning
DB2111 – New Application Features V11
Links to these courses may be found at: www.themisinc.com
Tony’s Email: [email protected]
Twitter: @ThemisTraining @TonyAndrews12
Newest Classes 2020
DB1101 – Db2 z/OS RESTful Services NEW!!!!!
DA1010 – Data Analytics using SQL New !!!!!
SQ1005 - Top 25+ Tuning Tips for Developers NEW!!!
Links to these courses may be found at: www.themisinc.com
Tony’s Email: [email protected]
Twitter: @ThemisTraining @TonyAndrews12
Education. Consulting Check Out
www.themisinc.com
• On-site and Public
• Instructor -led
• Hands-on
• Customization
• Experience
• Over 30 DB2 courses
• Over 400 IT courses
• P&T Consulting
US 1-800-756-3000
Intl. 1-908-233-8900