db2 sql & application programming · my favorite db2 sql enhancements for db2 v9-v12 presented...

My Favorite Db2 SQL Enhancements for

Db2 V9-V12

Presented by Tony Andrews, Themis Inc.

[email protected]

Twitter:

mailto:[email protected]

https://twitter.com/intent/follow?original_referer=https://about.twitter.com/resources/buttons&region=follow_link&screen_name=ThemisTraining&tw_p=followbutton

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.

[email protected]


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


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


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 ….


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



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


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


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'


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:


DENSE_RANK() OVER (ORDER BY SALARY DESC) AS R

FROM EMP

LASTNAME SALARY R

HAAS 52750.00 1


LUCCHESI 46500.00 2

THOMPSON 41250.00 3

V9 Row Numbering

Usage:


ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS R

FROM EMP

LASTNAME SALARY R

HAAS 52750.00 1


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





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'


;

SELECT * FROM EMP_TEMPORAL_CUR

FOR SYSTEM_TIME BETWEEN '2016-01-30-13.44.12.107358109000'

AND '2016-03-30-15.46.12.107358109000'


;

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,




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);



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


(IN P_IN_DEPTNO CHAR(3),


P1: BEGIN

DECLARE I INTEGER DEFAULT 1;




END P1


(IN P_IN_DEPTNO DEPTNO_LIST,


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 < ?


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;


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


...

DSNT232I -BC SUCCESSFUL REBIND FOR


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





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





'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,


}

}

' )

)

;

Insert Example 3 With JSON Column INSERT INTO EMPJSON





'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,


},

"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


;

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


;

{"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

[email protected]

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

http://www.themisinc.com/webinars


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

http://www.themisinc.com/webinars


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

db2 sql & application programming · my favorite db2 sql enhancements for db2 v9-v12 presented...

Documents