| View previous topic :: View next topic |
| Author |
Message |
TheGodfather
Beginner
Joined: 08 Jul 2003
Posts: 10
Topics: 3
Location: Tvm, Keralam, India
|
 Posted: Thu Mar 16, 2006 9:49 am Post subject: SQLCODE -104 from UPDATE query which runs everyday |
 |
|
One of the production jobs abended with SQLCODE -104. This ran fine when restarted. This is the first time this is happening. There is no characters in columns 73-80.
| Code: |
UPDATE SQLREGN.EMEDTMP1 EMT
SET EMT.ASSIGNED_TO_NM =
(SELECT BUY.BUYER_NAME
FROM SQLREGN.ALLOWHED ALH,
SQLREGN.COMISDFT MIS,
SQLREGN.BUYROPTN BUY
WHERE ALH.DIVISION = EMT.DIVISION
AND ALH.VENDOR = EMT.VENDOR
AND ALH.LOG = EMT.LOG
AND ALH.CREATION_USERID <> 'SPOT000'
AND MIS.USERID = ALH.CREATION_USERID
AND BUY.BUYER_NUM = MIS.BUYER_NUM
AND BUY.BUYER_NAME <> EMT.ASSIGNED_TO_NM
AND BUY.BUYER_NAME NOT LIKE 'AVAILABLE%')
WHERE EXISTS
(SELECT 1
FROM SQLREGN.ALLOWHED HED,
SQLREGN.COMISDFT COM,
SQLREGN.BUYROPTN BOP
WHERE HED.DIVISION = EMT.DIVISION
AND HED.VENDOR = EMT.VENDOR
AND HED.LOG = EMT.LOG
AND HED.CREATION_USERID <> 'SPOT000'
AND COM.USERID = HED.CREATION_USERID
AND BOP.BUYER_NUM = COM.BUYER_NUM
AND BOP.BUYER_NAME <> EMT.ASSIGNED_TO_NM
AND BOP.BUYER_NAME NOT LIKE 'AVAILABLE%')
|
The message from JOBLOG
| Code: |
SQL ERROR DURING EXECUTE IMMEDIATE
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: =
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 83 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000053' X'00000000' SQL DIAGNOSTIC
INFORMATION
|
I am confused on why this is happening. I would appreciate any insights from you guys.
_________________
The Godfather
"I'll make an offer he can't refuse." |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 16, 2006 10:02 am Post subject: |
|
|
TheGodfather,
One simple way to find out exactly which line is in error is to copy and paste the sql query into QMF and press F2 . If there is an error , QMF points it out with line and position of the error. See if you can find any error
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
TheGodfather
Beginner
Joined: 08 Jul 2003
Posts: 10
Topics: 3
Location: Tvm, Keralam, India
|
| Posted: Thu Mar 16, 2006 1:39 pm Post subject: |
|
|
Thanks Kolusu! But that is the first step we tried when we encountered the abend.
The interesting point is this is running in production for about 2 weeks now and ran fine when restarted. 
_________________
The Godfather
"I'll make an offer he can't refuse." |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Thu Mar 16, 2006 2:19 pm Post subject: |
|
|
| It looks like this update is executed in some DB2 utility program. Is that true? If so, can you post the JCL? |
|
| Back to top |
|
|
TheGodfather
Beginner
Joined: 08 Jul 2003
Posts: 10
Topics: 3
Location: Tvm, Keralam, India
|
| Posted: Fri Mar 17, 2006 4:17 am Post subject: |
|
|
Thanks for the response Bithead! The JCL step is given below.
| Code: |
//IKJEFT01 EXEC PGM=IKJEFT01
//STEPLIB INCLUDE MEMBER=UTSS01P
//DSNTRACE DD DUMMY
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=U
//SYSTSIN DD DSN=SFDEV1.DB2.USERLIB.SQL(DB2P),DISP=SHR
//DUMLVL DD DSN=&&DUMP,DISP=(,PASS),
// SPACE=(TRK,(0))
//XRES EXEC PGM=XMRDB2P
//DD1 DD DSN=&&RESDSN
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=U
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
UPDATE SQLDAT3.EMEDTMP1 EMT
SET EMT.ASSIGNED_TO_NM =
(SELECT BUY.BUYER_NAME
FROM SQLDAT3.WMALWHED ALH,
SQLDAT3.COMISDFT MIS,
SQLDAT3.WMBUYOPT BUY
WHERE ALH.RES_DIVISION = EMT.RES_DIVISION
AND ALH.VENDOR = EMT.VENDOR
AND ALH.LOG = EMT.LOG
AND ALH.CREATION_USERID <> 'SPOT000'
AND MIS.USERID = ALH.CREATION_USERID
AND BUY.BUYER_NUM = MIS.BUYER_NUM
AND BUY.BUYER_NAME <> EMT.ASSIGNED_TO_NM
AND BUY.BUYER_NAME NOT LIKE 'AVAILABLE%')
WHERE EXISTS
(SELECT 1
FROM SQLDAT3.WMALWHED HED,
SQLDAT3.COMISDFT COM,
SQLDAT3.WMBUYOPT BOP
WHERE HED.RES_DIVISION = EMT.RES_DIVISION
AND HED.VENDOR = EMT.VENDOR
AND HED.LOG = EMT.LOG
AND HED.CREATION_USERID <> 'SPOT000'
AND COM.USERID = HED.CREATION_USERID
AND BOP.BUYER_NUM = COM.BUYER_NUM
AND BOP.BUYER_NAME <> EMT.ASSIGNED_TO_NM
AND BOP.BUYER_NAME NOT LIKE 'AVAILABLE%');
//*
|
_________________
The Godfather
"I'll make an offer he can't refuse." |
|
| Back to top |
|
|
TheGodfather
Beginner
Joined: 08 Jul 2003
Posts: 10
Topics: 3
Location: Tvm, Keralam, India
|
| Posted: Fri Mar 17, 2006 4:25 am Post subject: |
|
|
Please let me know if you need the full JCL. I have given the exact query as well to make sure that we are not missing anything here.
The job abended again and the restart was also not successful. 
_________________
The Godfather
"I'll make an offer he can't refuse." |
|
| Back to top |
|
|
Cogito-Ergo-Sum
Advanced
Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA
|
| Posted: Fri Mar 17, 2006 8:47 am Post subject: |
|
|
uh...what is this ?
| Code: | | //XRES EXEC PGM=XMRDB2P |
_________________
ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes. |
|
| Back to top |
|
|
TheGodfather
Beginner
Joined: 08 Jul 2003
Posts: 10
Topics: 3
Location: Tvm, Keralam, India
|
| Posted: Fri Mar 17, 2006 11:19 am Post subject: |
|
|
I was thinking that this is the one which provides the dump information. I am not sure about this, this was my assumption. The name is given as
| Code: | //XRES EXEC PGM=XMR&DB2ID.
|
So the name changes with the region.
We use DB@UTIL. The job step given above is the expanded version of this one. The step in proc is given below.
| Code: | //PS0050 EXEC DB@SQL,
// COND=(4,LT),
// DB2ID=DB2P,
// ENV=P
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD DSN=SAFP.CNTLLIB(EMDB08),
// DISP=SHR
//*
|
_________________
The Godfather
"I'll make an offer he can't refuse." |
|
| Back to top |
|
|
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
| Posted: Fri Mar 17, 2006 11:21 am Post subject: |
|
|
TheGodfather,
Just curious, is this firing a dynamic SQL and is the column " EMT.ASSIGNED_TO_NM " NULLABLE ? also can the Select Criteria return a NULL and if so how is the Null value handled? Thanks |
|
| Back to top |
|
|
TheGodfather
Beginner
Joined: 08 Jul 2003
Posts: 10
Topics: 3
Location: Tvm, Keralam, India
|
| Posted: Mon Mar 20, 2006 2:59 am Post subject: |
|
|
Hi Suresh,
The column is not NULLABLE. I think this is taken care of by the preceding step and setting the condition code.
All,
Our DBA's found the issue. Our production regions use DB2 ver 7. In ver 7, we cant have aliases for the set statement. so instaed of
| Code: | | SET EMT.ASSIGNED_TO_NM = |
it must be | Code: | | SET ASSIGNED_TO_NM = |
Thanks all! Have a wonderful week ahead! 
_________________
The Godfather
"I'll make an offer he can't refuse." |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
