JDBC Connector for DB2 Z/OS optimization

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
clarcombe
Premium Member
Premium Member
Posts: 515
Joined: Wed Jun 08, 2005 9:54 am
Location: Europe

JDBC Connector for DB2 Z/OS optimization

Post by clarcombe »

In production we are noticing that the updates and inserts to our Z/OS
tables are performing slowly.

Scenario
we are deleting, inserting and updating many tables with a small number of rows each time.

I have included two screen shots of our parameters. The transaction
size is set to 10000 having previously been 20000 but we had to down
grade it for a Z/OS timeout issue.

Insert
https://ibb.co/eoms4a

Update
https://ibb.co/gqSzja

Delete
https://ibb.co/b32X4a

For example
A delete of 30000 rows runs at 148 rows/second with an unique index on the key. Surely this should run faster than this ?

Does anyone know of any optimization parameters that can be set to speed this up ?

Thanks
Colin Larcombe
-------------------

Certified IBM Infosphere Datastage Developer
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

have you tried a test job using the DB2 connector stage?

(your pics didn't work)
clarcombe
Premium Member
Premium Member
Posts: 515
Joined: Wed Jun 08, 2005 9:54 am
Location: Europe

Post by clarcombe »

We did try the DB2 connector but for some reason (I dont remember why) we moved to the JDBC connector. I think it was a warning issue.

I dont know why the pics dont work. They work well enough if you copy paste them. Maybe its the https
Colin Larcombe
-------------------

Certified IBM Infosphere Datastage Developer
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Just as an FYI, they don't work because the forum software doesn't recognize them as valid image links, pretty sure they have to end in something more like .png/jpg/gif and the like. Took off the tags so people can click on them... they still don't work for me here but they block a ton of sites at work. :(
-craig

"You can never have too many knives" -- Logan Nine Fingers
clarcombe
Premium Member
Premium Member
Posts: 515
Joined: Wed Jun 08, 2005 9:54 am
Location: Europe

Post by clarcombe »

We believe this is due to having numerous triggers on each table. We are investigating.
Colin Larcombe
-------------------

Certified IBM Infosphere Datastage Developer
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

How exactly did you calculate the rows per second?

The monitor will show you rows per second based upon the start time of the job, not the actual speed of the rows flowing once the stage gets the data.

I would not recommend using a jdbc if the db2 connector stage is available. If you are getting a warning message... address it.

Also, turning off the table mismatch check is not a wise thing to do. it's minimal overhead to the job but can save your bacon when the need arises.
clarcombe
Premium Member
Premium Member
Posts: 515
Joined: Wed Jun 08, 2005 9:54 am
Location: Europe

Post by clarcombe »

Im looking at the notes in the job.I think it was to do with an RCP issue. DB2 didn't like RCP
Colin Larcombe
-------------------

Certified IBM Infosphere Datastage Developer
clarcombe
Premium Member
Premium Member
Posts: 515
Joined: Wed Jun 08, 2005 9:54 am
Location: Europe

Post by clarcombe »

From the original developer.

JDBC : chosen because not all the records were processed with the DB2 connector and it gave no message or failure.

We couldn't wait for a patch so we went with JDBC instead and this worked.
Colin Larcombe
-------------------

Certified IBM Infosphere Datastage Developer
Post Reply