DB2 Abend -818 when using 'DSNTIAD'

kalyanp_80 · New User Joined: 03 Feb 2006 Posts: 41 Location: Hyderabad

Hi,

I want to run a job which deletes all the rows from a table and its subordinates. We are passing the list of tables whose rows are to be deleted thru a control card to a program.

This job is currently coded to execute in Production Enviornment as is. But I need to execute this job in test region. So, I made following changes to the job:

1) Created a new control card which has list of tables pointing to test region.
2) Created a new DB2 run card which points to test DB2 subsystem. But did not made any changes to program name and plan name.

Before Changes RUN Card:
DSN SYSTEM(DSNA) RETRY(2)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA71)
LIB('DB2PA.PL.RUNLIB.LOADLIB')

After cHANGES RUN CaRD:
DSN SYSTEM(DSN1) RETRY(2) --------> Changed Subsystem
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA71)
LIB('DB2PA.PL.RUNLIB.LOADLIB')

After making the above changes, I submitted the Job, but it abended with -818 DB2 return code. This is basically a timestamp mismatch error. I am not sure how to resolve it as the program which I am using 'DSNTIAD' is an IBM utility program and I don't have source code with me. Please help.

Thanks
Amith Patalay

vini_srcna · Posted: Tue Mar 13, 2007 3:06 pm

yes, That's a IBM Supplied free productivity aid sample program to execute the SQL dynamically. I suspect you may not be using the right load module. Check with your DBA. I wish you could also try by changing the paln name to DSNTAID if possible. Can you paste the control card here..?

kalyanp_80 · New User Joined: 03 Feb 2006 Posts: 41 Location: Hyderabad

Hi Vini,

I already pasted the DB2 Run card. If you are asking for control card which has list of tables, please see below:

DELETE FROM DSNA.PERSON_PROFILES_T;
DELETE FROM DSNA.SKILLS_T;
DELETE FROM DSNA.TRAINING_RELS_T;
DELETE FROM DSNA.NARRATIVES_T;
DELETE FROM DSNA.SUPPLEMENTALS_T;
DELETE FROM DSNA.REC_BUILD_RELS_T;
COMMIT;

Thanks
Amith

vini_srcna · Posted: Tue Mar 13, 2007 3:31 pm

Well, I was wondering how you are passing just the table names in the SYSIN. I see the DELETE sql statements which executes dynamically.

It's a sample program written in ASSEMBLER Language to execute the SQL statement dynamically. IBM ships this as a source code you should precompile, assemble,Link & bind it in order to use that. This will be performed as a part of installation process.

So all you need to do is get in touch with DBA and let him know about this.
He may suggest you to use the different load module or other programs like DSNTEP2 or DSNTEP4 or DSNTIAUL.

Note: This is assembler program. So it is called as assemble and not as compile.

kalyanp_80 · New User Joined: 03 Feb 2006 Posts: 41 Location: Hyderabad

Hi Vinay,

Thanks for your quick reply. I am just wondering how this job is running successfully in the production region. Whatever change I made is only the subsystem name.

Thanks
Amith

vini_srcna · Posted: Tue Mar 13, 2007 4:02 pm

Thats where the problem is. LOAD modules need not be in the same libraries in all the subsystem. Here SSIS may not be the only parameter you need to change. there could be few more things depending on your site configuration. I would suggest to serach other job which have run in this region using DSNTAID. check the loadlib and make changes appropriately. I'm sure you have nothing to do with this apart from approaching the right person.

vini_srcna · Posted: Tue Mar 13, 2007 4:05 pm

sorry, it should be SSID and not SSIS. I meant to say SUB SYSTEM ID.

kalyanp_80 · New User Joined: 03 Feb 2006 Posts: 41 Location: Hyderabad

Hi Vinay,

Thanks for all this information. Instead of DSNTAID, I used DSNTEP2 utility and it worked successfully.

Thanks
Amith Patalay