Hello All,
I have got an ETL stored procedure which performs the following steps and takes in the following input parameters:

Input Parameters:

IN SOURCE_SCHEMA VARCHAR(128)
, IN SOURCE_TABLE VARCHAR(128)
, IN TARGET_SCHEMA VARCHAR(128)
, IN TARGET_TABLE VARCHAR(128)
, IN EXCEPTION_SCHEMA VARCHAR(128)
, IN EXCEPTION_TABLE VARCHAR(128))
Steps:

1. Checks for delta rows between source table and target table (where a certain flag
column = 'N' in source table).
2. Delete any exceptions from the exception table (to store only the latest
exceptions).
3. Update Audit Tables.
4. Based on certain conditions, this delta flag column value is set to 'P', 'U' or
'S' in the source table (where delta flag column = 'N').
5. Where Delta Flag column value = 'P', load those rows into the target table.
6. Writes all these steps (along with exceptions) into a log file and then emails
that log file to respective recipients.

There are couple other steps involved between step 5 and step 6 but they are not quite related to the step I need help with. So currently, load statements are dynamically built:

SET STATEMENT = '';
SET STATEMENT ='LOAD FROM (SELECT ' || SELECT_COLS ||', CURRENT_TIMESTAMP AS REC_CRTN_TS, '''||ETL_JOB_ID||''' AS JOB_ID FROM '||SOURCE_SCHEMA||'.'||SOURCE_TABLE||' WHERE PROC_RAW_FG = ''P'' '|| ORDER_BY_CLAUSE ||' FOR FETCH ONLY ) OF CURSOR MESSAGES ON SERVER INSERT INTO '||TARGET_SCHEMA||'.'||TARGET_TABLE || ' FOR EXCEPTION '||EXCEPTION_SCHEMA||'.'||EXCEPTION_TABLE;
CALL DBMS_OUTPUT.PUT_LINE(STATEMENT); -- for logging purposes into a file
CALL UTL_FILE.PUT_LINE(OUTFILE,UTL_TCP.CRLF||'LOAD STATEMENT :'||UTL_TCP.CRLF||STATEMENT); -- for logging purposes into a file
SELECT_COLS is a variable that holds comma separated source fields with logic to trim char/varchar fields for select clause to apply to target table. It would look something like this (column list is huge, so only including a few): TRIM(TSO_SPLIT_NO) AS TSO_SPLIT_NO,QTY_SPLIT,TRIM(HIGH_PRTY_INDC) AS HIGH_PRTY_INDC,TRIM(QTE_MATCH_CNTL_CD) AS QTE_MATCH_CNTL_CD

Also, this column list depends on what source table and target table we have passed as inputs, following logic is implemented to extract the column list:

SET STATEMENT = '';
SET STATEMENT = 'SET (?,?,?,?,?) = (SELECT SELECT_COLUMNS, ORDER_BY_CLAUSE, PRIMARY_KEY_COLUMNS, SUSP_REC_WHERE_CLAUSE,SOURCE_COLS FROM schemaName.tableName WHERE SOURCE_SCHEMA = '''|| SOURCE_SCHEMA ||''' AND SOURCE_TABLE = '''|| SOURCE_TABLE ||''' AND TARGET_SCHEMA= '''|| TARGET_SCHEMA ||''' AND TARGET_TABLE = '''|| TARGET_TABLE ||''') ';
PREPARE C_STATEMENT FROM STATEMENT;
EXECUTE C_STATEMENT INTO SELECT_COLS, ORDER_BY_CLAUSE, PRIMARY_COLS, SUSP_REC_WHERE_CLAUSE, SOURCE_COLS;
ETL_JOB_ID is a variable that holds dynamically generated value based on the following:

SET MAIN_STEP_CD = THIS_PROC||'.'||TARGET_TABLE;
SET ETL_JOB_ID = MAIN_STEP_CD ||'_'|| TO_CHAR(BEGIN_TS,'YYYYMMDDHH24MISS');

Now, since we are using DB2 Load Utility, this step often causes blocking since this step locks up the entire table, not just the rows that it is working with. That said, I was instructed to use a cursor to insert records into target table, of course, without hard coding any of the stuff as that would make this stored procedure unusable.

I do have something in mind but I am having difficulties in constructing the actual cursor. More specifically, how do I fetch dynamically input column values and then dynamically insert them into the target table? Also, I couldn't think of a way to catch expections (like we have a FOR EXCEPTION clause in the load statement) I was thinking it is going to look something like this:

DECLARE EOFR INT DEFAULT 0;
DECLARE CR_SELECT_COLS ANCHOR DATA TYPE TO schemaName.tableName.SELECT_COLUMNS
DECLARE CR_REC_CRTN_TS TIMESTAMP;
DECLARE CR_ETL_JOB_ID ANCHOR DATA TYPE TO schemaName.tableName.ETL_JOB_ID;
DECLARE STMT VARCHAR(200);
DECLARE STMT2 VARCHAR(200);

SET STMT ='SELECT ' || SELECT_COLS ||', CURRENT_TIMESTAMP AS REC_CRTN_TS, '''||ETL_JOB_ID||''' AS JOB_ID FROM '||SOURCE_SCHEMA||'.'||SOURCE_TABLE||' WHERE PROC_RAW_FG = ''P'' '|| ORDER_BY_CLAUSE || ' FOR FETCH ONLY'
PREPARE S1 FROM STMT;

DECLARE C1 CURSOR FOR
EXECUTE S1;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET EOFR = 1;

SET STMT2 = 'INSERT INTO '||TARGET_SCHEMA||'.'||TARGET_TABLE || ' VALUES (?,?,?)';
PREPARE S2 FROM STMT2;
OPEN C1;
WHILE EOFR = 0 DO
FETCH FROM C1 INTO CR_SELECT_COLS, CR_REC_CRTN_TS, CR_ETL_JOB_ID;
EXECUTE S2 USING CR_SELECT_COLS, CR_REC_CRTN_TS,CR_ETL_JOB_ID;
END WHILE;
CLOSE C1;

But I think this is not going to work as CR_SELECT_COLS will not be able to hold multiple column VALUES. That said, based off of my DB2 knowledge (which is way down there at the moment, since DB2 LUW platform is not my expertise yet), this is the only way I could think of implementing a cursor. If you guys think there is a better way, please feel free to share. Thanks in advance!

Views: 1141

Reply to This

Replies to This Discussion

Gaurav,

Does LUW have a concurrent LOAD that does actually INSERT under the covers? That could be easy solution if was on z/OS.

Can you explain what types of Exceptions are expected? Will an entire run fail or individual rows will have problems? What is current technique that determines the Delta between source and target? That could be harder to do than the load of selected rows.

What is maximum number of rows to be Inserted?

It is going to be easier to construct an INSERT FROM SELECT to insert multiple rows at a time, either all of them, or batch of n rows using FETCH FIRST n ROWS only in a loop, or even case n = 1. INSERT VALUES after a FETCH from Cursor is probably too hard to code. 

Is there something to be updated in the source table to indicate delta 'P" rows have already been copied?  If not, it is tricky to SELECT only the rows that have not already been copied. Perhaps INSERT from SELECT cols FROM source EXCEPT SELECT cols from Target FETCH FIRST n ROWS ONLY.

Your question probably has too many complex parts to it. Simplification of the question might help.

RSS

Latest Activity

Carol Davis-Mann posted an event

Db2 for z/OS: The Evolution Engine Webcast at United Kingdom

March 16, 2021 from 11am to 11:30am
Join Julian Stuhler, IBM Gold Consultant and Solutions Delivery Director at Triton Consulting for his first IBM hosted webcast of 2021.Julian will be presenting Db2 for z/OS: The Evolution Engine on Tuesday, March 16th at 15:00 GMT (11:00 AM EDT).Presentation OverviewFor over 35 years, Db2 for z/OS has been the engine at the heart of many of the critical applications that run the world around us.…See More
1 hour ago
Carol Davis-Mann posted a blog post

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part Two

In DB2 12 for z/OS, DRDA Applications and Application Compatibility Part Two Gareth Copplestone-Jones provides guidance on the implementation of server-side configuration.Server-side configurationWhen considering how to manage managing Application Compatibility – APPLCOMPAT – for your distributed applications which use the NULLID packages, the main alternative to client-side configuration (discussed in the …See More
21 hours ago
An event by Jonathan Sloan was featured
Thumbnail

IBM Db2 for z/OS Data Gate and IBM Cloud Pak for Data on IBM Z update at Webcast - East Coast U.S. time zone

March 9, 2021 from 11am to 12pm
Access your Db2 for z/OS Data from the cloud: Db2 Data Gate and IBM Cloud Pak for Data on IBM Z demo and updateMarch 9th @ 11:00 AM Eastern U.S. timeRegister now @ https://ibm.biz/Db2DGUpdateWoDJoin us to see a demo of Db2 for z/OS Data Gate which can help you reduce the complexity associated with delivering Db2 for z/OS data for new hybrid cloud initiatives. Db2 Data Gate allows you to keep pace with new cloud, analytics and mobile initiatives with…See More
21 hours ago
William Shipley liked Surekha Parekh's event Db2 for z/OS: The Evolution Engine - Julian Stuhler
22 hours ago
Dragica Smintic liked Surekha Parekh's event Db2 for z/OS: The Evolution Engine - Julian Stuhler
yesterday
A blog post by Surekha Parekh was featured
yesterday
An event by Surekha Parekh was featured

Db2 for z/OS: The Evolution Engine - Julian Stuhler at Webcast

March 16, 2021 from 11am to 12:30pm
Register NowDb2 for z/OS: The Evolution Engine - Julian StuhlerFor over 35 years, Db2 for z/OS has been the engine at the heart of many of the critical applications that run the world around us. From internet banking transactions to airline reservations and insurance quotations, Db2 for z/OS continues to power a huge array of today’s high volume, mission-critical enterprise applications. But how has Db2 remained so relevant…See More
yesterday
Tom Glaser posted events
yesterday
Jonathan Sloan posted an event
Thumbnail

IBM Db2 for z/OS Data Gate and IBM Cloud Pak for Data on IBM Z update at Webcast - East Coast U.S. time zone

March 9, 2021 from 11am to 12pm
Access your Db2 for z/OS Data from the cloud: Db2 Data Gate and IBM Cloud Pak for Data on IBM Z demo and updateMarch 9th @ 11:00 AM Eastern U.S. timeRegister now @ https://ibm.biz/Db2DGUpdateWoDJoin us to see a demo of Db2 for z/OS Data Gate which can help you reduce the complexity associated with delivering Db2 for z/OS data for new hybrid cloud initiatives. Db2 Data Gate allows you to keep pace with new cloud, analytics and mobile initiatives with…See More
yesterday
Michal Bialecki liked Carol Davis-Mann's blog post DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One
Feb 19
A blog post by Carol Davis-Mann was featured

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One

IntroductionThis, the first of two articles on how to manage the Application Compatibility level for DRDA applications, provides an introduction to the subject and considers two of the ways of doing this. In the second article Gareth Copplestone-Jones will concentrate on perhaps the most promising method and discusses its drawbacks.A very brief history of Application CompatibilityWith the release of DB2 11 for z/OS, IBM introduced Application Compatibility, which is intended to make migration…See More
Feb 19
An event by Surekha Parekh was featured
Thumbnail

Business and Technical Value of Top Ten Features in Db2 12 for z/OS Continuous Delivery at Virtual Event

March 23, 2021 from 11am to 12:15pm
REGISTER NOWBusiness and Technical Value of Top Ten Features in Db2 12 for z/OS Continuous Delivery Abstract One of the most valuable resources for modern businesses is business critical data.  Data can be used to provide insight into every facet of the business from operations to market trends. Powerful data enables effective and efficient business decision making and your enterprise data is only as powerful as your ability to harness it. Storing,…See More
Feb 19
Carol Davis-Mann posted a blog post

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One

IntroductionThis, the first of two articles on how to manage the Application Compatibility level for DRDA applications, provides an introduction to the subject and considers two of the ways of doing this. In the second article Gareth Copplestone-Jones will concentrate on perhaps the most promising method and discusses its drawbacks.A very brief history of Application CompatibilityWith the release of DB2 11 for z/OS, IBM introduced Application Compatibility, which is intended to make migration…See More
Feb 17
An event by Surekha Parekh was featured
Thumbnail

Db2 12 agile, open and secure - Your database of choice for business critical data at Webcast

March 16, 2021 from 11am to 12:30pm
Register Now    PART 2Maria Sueli from the IBM Development Lab Following Julian, Maria Sueli from the IBM Development Lab will share with you how you can remove barriers that are holding your Db2 enterprise data captive in outdated methods and processes.  She will demonstrate how Db2 for z/OS…See More
Feb 17
Annette Zawacki added a discussion to the group Data, Analytics and AI on IBM Z
Thumbnail

Don't miss the first Db2 Analytics Accelerator Webcast of 2021

IBM Db2 Analytics Accelerator Trends and DirectionsFebruary 16 / 11:00 AM ETRegister todayPlease join IBM for a webcast where we will share the latest Db2…See More
Feb 10
Surekha Parekh updated an event
Thumbnail

28. DeDUG at virtual

February 26, 2021 from 9am to 12pm
The 28. DeDUG regional user group meeting will take place on Friday 26.02.2021 as a vitual event. Agenda and registration for this free event can be found here. We are looking forward to…See More
Feb 8

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

Replaying past database activities

Started by Shaun Hide in Security and DB2. Last reply by Alexander Dashevsky Nov 17, 2020. 1 Reply

I would need to know who modified table data on a certain day.I do have my backups and database logs available.DB2audit was however not activated at this point.How would i  be able to access this information?Continue

Tags: #DataAccess

Db2 is developer inclusive and preview of Db2 11.5.5

Started by Kip in What's hot ? Nov 4, 2020. 0 Replies

This digital event examines how enterprises can become smarter with a data management system that is developer-inclusive. During this session, participants will get to learn how Db2 integrates with a broad array of programming languages, employs…Continue

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service