| View previous topic :: View next topic |
| Author |
Message |
mfuser
Banned
Joined: 01 Mar 2005
Posts: 105
Topics: 58
|
 Posted: Thu Jun 15, 2006 11:07 am Post subject: updating table column through JCL |
 |
|
Hai All,
I have a table as of now in which one of the column has some values and my intention is to move spaces to all the rows of the table for that particular column and i want to do through a JCL only.
I am in the view of using this logic:
1.Unloading the data from a table into a dataset.
2.Make use of SORT and use OUTREC BUILD to move spaces to the desired field.
3.Load the table from the SORTOUT dataset.
I want to know are there any other ways of doing this particular task efficiently and any body could help me all possible ways to do this job would really be helpful to me.
Moreover i have one more query as we unload DB2 data to a dataset by extracting using SELECT query ,can we make use of a UPDATE statement and update the column in the table through JCL.Please help me out if i am wrong in my understanding. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Thu Jun 15, 2006 11:16 am Post subject: |
|
|
mfuser,
The following JCL will give you the results.
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSIN DD *
DSN SYSTEM(xxxx)
RUN PROGRAM(DSNTEP2) -
PLAN(DSNTEP2) -
LIB('DB2P.RUNLIB.LOAD')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
UPDATE TABLE
SET COL = ' '
WHERE COND1 = 'your condition'
;
/*
|
| Quote: |
Moreover i have one more query as we unload DB2 data to a dataset by extracting using SELECT query ,can we make use of a UPDATE statement and update the column in the table through JCL.
|
Update the table using the above JCL and then unload the data using DSNTIAUL.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mfuser
Banned
Joined: 01 Mar 2005
Posts: 105
Topics: 58
|
| Posted: Thu Jun 15, 2006 12:12 pm Post subject: |
|
|
Kolusu,
Thanks for your early reply .I had understood the code but when i run the job the job gets successfully completed but does not update the column field and in my SPOOL i get the output:
Can u tell me what is DSNTEP2 and it's use in this context ? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Thu Jun 15, 2006 12:23 pm Post subject: |
|
|
mfuser,
Did you look at the SYSPRINT DD after the successful completion of the job? if your job ended with a return code of zero, then you should see something like this in your sysprint DD output
| Code: |
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT416I SQLERRD = 0 0 nnn -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'000000FD' X'FFFFFFFF'
INFORMATION
SUCCESSFUL UPDATE OF nnn ROW(S)
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mfuser
Banned
Joined: 01 Mar 2005
Posts: 105
Topics: 58
|
| Posted: Thu Jun 15, 2006 12:49 pm Post subject: |
|
|
Kolusu,
It was really a good experience from your reply and i am able to get the desired results as expected .Actually i forgot to use SYSPRINT in the JCL and did not use continuation indicator '-' before the LIB statement.
OUTPUT
| Code: |
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT416I SQLERRD = 0 0 1 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000001' X'FFFFFFFF' X'000
INFORMATION
SUCCESSFUL UPDATE OF 46 ROW(S)
|
Can you please tell me what is DSNTEP2 and the use of this UTILITY ? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
jayant_tibhe
Beginner
Joined: 24 Feb 2006
Posts: 8
Topics: 2
Location: PUNE
|
| Posted: Tue Oct 17, 2006 6:05 am Post subject: |
|
|
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSIN DD *
DSN SYSTEM(xxxx)
RUN PROGRAM(DSNTEP2) -
PLAN(DSNTEP2) -
LIB('DB2P.RUNLIB.LOAD')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
UPDATE TABLE
SET COL = ' '
WHERE COND1 = 'your condition'
;
/*
|
Hi Kolusu,
While running the above JCL I am getting the following error....
_________________
Thanks and regards,
Jayant S Tibhe |
|
| Back to top |
|
|
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Tue Oct 17, 2006 6:20 am Post subject: |
|
|
jayant_tibhe,
Check whether you have the IBM supplied utility DSNTEP2 in the current Library in your shop which you are coding in the LIB(XXXX) .
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
jayant_tibhe
Beginner
Joined: 24 Feb 2006
Posts: 8
Topics: 2
Location: PUNE
|
| Posted: Tue Oct 17, 2006 11:45 pm Post subject: |
|
|
Shekhar The library is correct..Only thing is that I am not sure about
DSN SYSTEM(xxxx)
How to find that ....Please help me
_________________
Thanks and regards,
Jayant S Tibhe |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Wed Oct 18, 2006 2:50 am Post subject: |
|
|
The source for the name of your DB2 subsystem can be a system programmer, or you look in your ISPF Panels for the DB2 interface of IBM (DB2I primary option menu). In the upper right corner of the panel your subsystem name is displayed.
regards
Christian |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
