MVSFORUMS.com

[pvrajs]

Hi,

I have a DB2 table that is loaded with the help of LOAD utility. I understand till Ver 8.1 the tablespace is exclusively locked for the table to be loaded. In the current scenerio, the table is being accessed by other distributed applications at the time the table is loaded. So, either the distributed applications are not able to retrieve data or the batch job fails. So, we thought of replacing the LOAD utlity with INSERT statements. Please note that amount of data being loaded is NOT huge.

The LOAD utility step is also the TERM Utility. I would like to know if it is necessary to use the term utility if we are using the COBOL-DB2 program with INSERT statements in place of LOAD utility. If so, how? Also please let me know if there is any other alternatives.

Please clarify.

Thanks,
Vijay

[kolusu]

[pvrajs]

Hi Kolusu,

Thanks for the reply. Could you please provide me the link to understand the theoritical back ground. That would help me to understand things better before i work on the proposed solution. My requirement slightly varies with the other postings (i think).

1. Insert if a new record is not available in the table.
2. Update if the record is availble in the table.
3. Delete the record from the table if the record is not present in the file.

Thanks,
Vijay

[pvrajs]

Hi,

After going thru the details provided, I understand that the below mentioned steps are to be performed to get out of the problem I am facing -

1. Generate the Delete control cards for the i/p file
2. Execute the DSNTIAUL utility to UNLOAD the data from the table
3. Generate the Insert control card for the i/p file
4. Run the updates

Please correct me if I am wrong.

This leaves me with some queries -

a. How can I ensure that all the data in the table has been Unloaded by DSNTIAUL? There may be a case that some of the key values present in the table are not present in the i/p file. And so are not deleted. Can I write a DELETE control card to delete everything from the table, by putting Delete * ... Currently we are flushing out the data and reloading it every day with LOAD
b.

[kolusu]

pvrajs,

Since you want to update/delete/insert I suggest you this procedure.

1. Unload the entire data from the table using DSNTIAUL. The utility unloads all the records from the table if your select statement is as follows

[pvrajs]

Thanks kolusu.

Can you please address my questions 'b' and 'c' in my earlier thread on locking and consistency?

Thanks,
Vijay

[kolusu]

[pvrajs]

Thanks for the clarifications Kolusu.

Hopefully a last query. I have some COMP-3 data in one of the datasets that is being loaded to the database into smallint type of column. As per my knowledge, the LOAD utility implicitly takes care of the data conversions.

How can the conversion of COMP-3 to smallint / any other datatype be achieved when using INSERT/UPDATE statements?

Thanks,
Vijay

[kolusu]

pvrajs,

[pvrajs]

Yes, I am sure. Please find the details below -

DCLGEN

[kolusu]

what makes you think the input file has COMP-3 values? Run the following job and show us the contents in hex format.

[pvrajs]

Cond from the prev thread ..

Please find the table definition below

CREATE TABLE <table_name>
(C1 CHAR ( 0001 ) NOT NULL ,
C2 CHAR ( 0008 ) NOT NULL ,
C3 CHAR ( 0004 ) NOT NULL ,
C4 CHAR ( 0004 ) NOT NULL ,
C5 SMALLINT NOT NULL )
IN <table_space>
AUDIT NONE DATA CAPTURE NONE ;
_________________
Thanks for help

[pvrajs]

Please find the values below -

00CF
003C
0000
0000
0000
0000

I am thinking to use the code you have suggested in threads of your first reply to create the SQLCARDS

[kolusu]

pvrajs,

From the above data I don't think the input is comp-3.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[pvrajs]

What values could it be then?

Thanks,
Vijay