MVSFORUMS.com

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

Hi,

We have a certain set of input files which need to mixed & matched with Db2 table information. Basically I can visualize this file as a table which is joined with another DB2 table on a common key.

Currently we are writing DB2 programs which reads each record from a file at a time & uses cursors to look up relevant records from the DB2 table.

I was thinking if there is a better solution to retrieve all matched records from DB2 table in one go instead of using cursors. Basic motive is to increase efficiency (I am assuming here it will give better results, please correct me if I am wrong).

Please suggest.
_________________
-Hatim M P

CraigG · Posted: Wed Feb 06, 2008 7:35 pm Post subject:

Just how are planning on joining a table and a file?

Terry_Heinze · Posted: Wed Feb 06, 2008 10:13 pm Post subject:

For a relatively small number of records on your file, I've used your method of building a cursor for each record. For large files, you might consider unloading the DB2 table and matching the unload file to your input file on the relevant key after sorting both files of course.
_________________
....Terry

vkphani · Intermediate Joined: 05 Sep 2003 Posts: 483 Topics: 48

Why don't you upload the file into a table and use inner join between both the tables.

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

Well I dont have corresponding tables for those files. I did see something of this sort on this forum
DECLARED GLOBAL TEMPORARY TABLE.

"Just how are planning on joining a table and a file?"

I dont know if this can be done, that's why the question....

As of now we are doing file based mix n match after unloading the table, updating it and reloading it, but then it creates problems for concurrency as the same tables are being used online as well.

Thanks!
-Hatim.
_________________
-Hatim M P

videlord · Beginner Joined: 09 Dec 2004 Posts: 147 Topics: 19

Temporary table is one solution

DFSORT with SPLICE can be considered if the JOIN is not too complex, unload the table to file too

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

Hi Videlord,

we do have Sort joinkeys option that we are using where appropriate, but the question of concurrency still remains.
We have
1. Unload job
2. Mix n Match with Update
3. Load job

Between 1 & 3 the entire table has to be offline. Also in between if one of the job abends, the table will be offline till the abend is resolved.

I was wondering if its possible to achieve concurrency & efficiency in one go. Perhaps this can be achieved by working on the tables itself rather then files but at the same time avoiding too many connections to the DB.
_________________
-Hatim M P

videlord · Beginner Joined: 09 Dec 2004 Posts: 147 Topics: 19

if the tabe are changing when do the join, then the result may change,depends on when to join , i think your application can handle it.

For concurrency consideratoin, you have to use DBMS.
Create temporary table or declared temporary table is effeciient than regular table if the table used to store temp data only

Terry_Heinze · Posted: Thu Feb 07, 2008 2:30 pm Post subject:

The size of your input file and the size of your cursors will help determine your solution. What are they? Also, do you update the DB2 table after the join or is it read only?
_________________
....Terry

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

The Size of the file as of now is between 50K to 100K rows, table size is same. These tables (including the project) are just 7 months old & they will grow 3-4 times in coming year or so.

Yes we do update the table after join.
_________________
-Hatim M P

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

The Size of the file as of now is between 50K to 100K rows, table size is same. These tables (including the project) are just 7 months old & they will grow 3-4 times in coming year or so.

Yes we do update the table after join.
_________________
-Hatim M P

Terry_Heinze · Posted: Thu Feb 07, 2008 3:52 pm Post subject:

Building a cursor between 50,000 and 100,000 times during a program is going to really tax DB2. I'd suggest defining a DB2 table to load your input file into then joining as vkphani suggested.
_________________
....Terry

CraigG · Posted: Fri Feb 08, 2008 2:29 pm Post subject:

Why do you need to use a cursor for each record in the file? Isn't there enought information in a record to identify what you need from the table?

Terry_Heinze · Posted: Fri Feb 08, 2008 4:50 pm Post subject:

I was assuming from Hatim's 1st post that each input record contains a unique key that, when used to build a cursor, would result in a number of rows on the DB2 table that match that key.
_________________
....Terry