By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,159 Members | 1,880 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,159 IT Pros & Developers. It's quick & easy.

I hope in Serge Rielau

P: n/a
Ciao a tutti ( Hi to all )

( sorry for my bad english ) !

I have a problem with dynamic sql. I try to read all internet but I don't
understand anything ! So I hope in Serge ( I love toronto in particular
missaugua )
I write the error in my log file :
"
2006.6.8 - 15:36.9 - [413] - INFO - --> Lo statment di insert e'
insert into inventari_det(
NVD_NVT_NUMERO_DOCUMENTO,NVD_NUMERO_RIGA,NVD_UBI_L OC_LOCALITA_1,NVD_UBI_UBICAZIONE_1,NVD_PRIMO_LIVEL LO_1,NVD_SECONDO_LIVELLO_1,NVD
_TERZO_LIVELLO_1,NVD_QUARTO_LIVELLO_1,NVD_ANA_TIPO _ANAGRAFICA,NVD_ANA_CODICE_SAP,NVD_STATO_MATERIALE ,NVD_TIPO_MATERIALE,NVD_TRZ_TIPO_TERZO,NVD_TRZ_COD ICE_TER
ZO,NVD_RDA,NVD_SERIAL_NUMBER_1,NVD_QUANTITA_1,NVD_ GST_PROGRESSIVO_INTERNO,NVD_ORDINE_USCITA_1,NVD_RI GA_ORDINE_USCITA_1,NVD_ETICHETTA_1,NVD_GERARCHIA,N VD_GST_
GST_PROGRESSIVO_INT,NVD_DATA_CREAZIONE,NVD_UTENTE_ CREAZIONE) select 64 as
documento ,row_number() over() as
numriga,DWH_MAG_UBI_LOC_LOCALITA,DWH_MAG_UBI_UBIC
AZIONE,DWH_MAG_PRIMO_LIVELLO,DWH_MAG_SECONDO_LIVEL LO,DWH_MAG_TERZO_LIVELLO,DWH_MAG_QUARTO_LIVELLO,DW H_ANA_TIPO_ANAGRAFICA,DWH_ANA_CODICE_SAP,DWH_STATO _PALLET
,DWH_TIPO_STOCK,DWH_TRZ_TIPO_TERZO,DWH_TRZ_CODICE_ TERZO,DWH_RIFERIMENTO_RDA,DWH_SERIAL_NUMBER,DWH_QU ANTITA,DWH_PROGRESSIVO_INTERNO,DWH_NUMERO_ORDINE,D WH_NUME
RO_RIGA,DWH_NUMERO_ETICHETTA,DWH_GERARCHIA,DWH_GST _PROGRESSIVO_INTERNO,current
date as dater,'InvCreaDet' as unamet from dwh_stock x where
DWH_ANA_TIPO_ANAGRAFICA in ( substr( 'S' , posstr( 'S' , '-' ) + 1 ) ,
substr( 'S' , posstr( 'S' , '-' ) + 1 ) ) AND
DWH_TIPO_STOCK in ( substr( 'C' , posstr( 'C' , '-' ) + 1 ) , substr(
'C' , posstr( 'C' , '-' ) + 1 ) ) AND
DWH_STATO_PALLET in ( substr( 'L' , posstr( 'L' , '-' ) + 1 ) ,
substr( 'L' , posstr( 'L' , '-' ) + 1 ) ) AND
DWH_MAG_UBI_LOC_LOCALITA in ( substr( 'WF1' , posstr( 'WF1' , '-' ) + 1 )
, substr( 'WF1' , posstr( 'WF1' , '-' ) + 1 ) ) AND
DWH_MAG_UBI_UBICAZIONE in ( substr( 'WF1-SAM1' , posstr( 'WF1-SAM1' ,
'-' ) + 1 ) , substr( 'WF1-SAM1' , posstr( 'WF1-SAM1' , '-' ) + 1 ) )
AND
DWH_NVT_NUMERO_DOCUMENTO is null
AND DWH_DATA_REGISTRAZIONE <= date('04/04/2006') and
DWH_DATA_REGISTRAZIONE = ( select max(a.DWH_DATA_REGISTRAZIONE) from
dwh_stock a where a.DWH_DATA_REGISTRAZIONE <= date ('04/04/2006') and
x.DWH_PROGRESSIVO_INTERNO = a.DWH_PROGRESSIVO_INTERNO )
Lunghezza(2014)
2006.6.8 - 15:36.9 - [418] - INFO - --> Prima della prepare
2006.6.8 - 15:36.9 - [422] - ERR! - --> SQLCODE = -104 SQLSTATE[42601]
2006.6.8 - 15:36.9 - [424] - ERR! - --> SQLERRMC = = IVO_INTERNO  JOIN
SQLNP012^└^Z (19) SQLSTATE[42601]
2006.6.8 - 15:36.9 - [426] - ERR! - --> SQLERRP = SQLNP012^└^Z
SQLSTATE[42601]
2006.6.8 - 15:36.9 - [428] - ERR! - --> PROBLEMA NELLA PREPARAZIONE DELLA
INSERT IN INVENTARI_DET SQLSTATE[42601]
2006.6.8 - 15:36.9 - [138] - ERR! - --> Errore nell'aperto |?
SQLSTATE[00000]
"
So this dml is dynamic sql. The variable sql_insert, that contain the string
is declare like char sql_insert[5000] ; this statment is 2014 char long !!
so someone help me ?
I trust in Serge

regards
sergio
Jun 8 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Something is fishy here.
Are you 100% sure this is the statement DB2 is sent?
Is this a development machine? Try setting the diag level to 4
and check whether the statement is dumped to the log.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #2

P: n/a
Thanks you very much. I don't know how to set diaglog to 4, but tommorow I
ask to a collugue of my
for this moment, unless you can able to tell me how to set dialog to 4, I
say you thanks

regards
sergio

"Serge Rielau" <sr*****@ca.ibm.com> ha scritto nel messaggio
news:4e*************@individual.net...
Something is fishy here.
Are you 100% sure this is the statement DB2 is sent?
Is this a development machine? Try setting the diag level to 4
and check whether the statement is dumped to the log.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Jun 8 '06 #3

P: n/a
eap90210 wrote:
Thanks you very much. I don't know how to set diaglog to 4, but tommorow I
ask to a collugue of my
for this moment, unless you can able to tell me how to set dialog to 4, I
say you thanks

regards
sergio

"Serge Rielau" <sr*****@ca.ibm.com> ha scritto nel messaggio
news:4e*************@individual.net...
Something is fishy here.
Are you 100% sure this is the statement DB2 is sent?
Is this a development machine? Try setting the diag level to 4
and check whether the statement is dumped to the log.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


UPDATE DBM CFG USING DIAGLEVEL 4

Note that DB2 will get REALLY CHATTY. So don't forget to unset:

UPDATE DBM CFG USING DIAGLEVEL 3

It can in fact reduce performance to leave it on.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #4

P: n/a
Big thanks again.
After DIAGLEVEL=4 , in the db2diag.log file I find this information
[cut]
2006-06-09-14.24.25.571166+120 I20992C949 LEVEL: Error
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : Hexdump, 136 bytes
0x20001328 : 5351 4C43 4120 2020 0000 0088 FFFF FF98 SQLCA ........
0x20001338 : 0013 3DFF 4956 4F5F 494E 5445 524E 4F20 ..=.IVO_INTERNO
0x20001348 : FF4A 4F49 4E20 2020 2020 2020 2020 2020 .JOIN
0x20001358 : 2020 2020 2020 2020 2020 2020 2020 2020
0x20001368 : 2020 2020 2020 2020 2020 2020 2020 2020
0x20001378 : 2020 2020 2020 2020 5351 4C4E 5030 3132 SQLNP012
0x20001388 : 801A 006D 0000 0000 0000 0000 0000 0000 ...m............
0x20001398 : FFFF FD3F 0000 0000 2020 2020 2020 2020 ...?....
0x200013A8 : 2020 2034 3236 3031 42601
[cut]

2006-06-09-14.24.25.658745+120 I22946C332 LEVEL: Info
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
MESSAGE : SQO semop=> pSSemSetID:
DATA #1 : Hexdump, 4 bytes
0x20C239FC : 017C 000F .|..

2006-06-09-14.24.25.659124+120 I23279C390 LEVEL: Info
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
MESSAGE : CCI Error:
DATA #1 : Hexdump, 4 bytes
0x2FF22328 : 0000 0047 ...G

2006-06-09-14.24.25.675458+120 I23670C377 LEVEL: Info
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
RETCODE : ZRC=0x8136001C=-2127167460=SQLZ_RC_NO_CONNECTION, SQLT_SQLJC
"No connection"

2006-06-09-14.24.25.716831+120 I24048C550 LEVEL: Info
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
DATA #1 : String, 242 bytes
CALL STACK:
[0] 0xD1537544 sqljcReceive__FP10sqljCmnMgr + 0x158
[1] 0xD154A890 sqljrDrdaArDisconnect__FP7UCintfc + 0x1C0
[2] 0xD15751FC sqleUCdisconnect + 0x64
[3] 0xD1574EFC sqleUCtermAllCtx + 0x210
[4] 0xD1528558 sqleterm__FcP5sqlca + 0x50

Now Do you have any idea, suggest or other for to help me ?

best thanks and regards
sergio

"Serge Rielau" <sr*****@ca.ibm.com> ha scritto nel messaggio
news:4e*************@individual.net...
eap90210 wrote:
Thanks you very much. I don't know how to set diaglog to 4, but tommorow
I ask to a collugue of my
for this moment, unless you can able to tell me how to set dialog to 4, I
say you thanks

regards
sergio

"Serge Rielau" <sr*****@ca.ibm.com> ha scritto nel messaggio
news:4e*************@individual.net...
Something is fishy here.
Are you 100% sure this is the statement DB2 is sent?
Is this a development machine? Try setting the diag level to 4
and check whether the statement is dumped to the log.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


UPDATE DBM CFG USING DIAGLEVEL 4

Note that DB2 will get REALLY CHATTY. So don't forget to unset:

UPDATE DBM CFG USING DIAGLEVEL 3

It can in fact reduce performance to leave it on.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Jun 9 '06 #5

P: n/a
eap90210 wrote:
Big thanks again.
After DIAGLEVEL=4 , in the db2diag.log file I find this information
[cut]
2006-06-09-14.24.25.571166+120 I20992C949 LEVEL: Error
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : Hexdump, 136 bytes
0x20001328 : 5351 4C43 4120 2020 0000 0088 FFFF FF98 SQLCA ........
0x20001338 : 0013 3DFF 4956 4F5F 494E 5445 524E 4F20 ..=.IVO_INTERNO
0x20001348 : FF4A 4F49 4E20 2020 2020 2020 2020 2020 .JOIN
0x20001358 : 2020 2020 2020 2020 2020 2020 2020 2020
0x20001368 : 2020 2020 2020 2020 2020 2020 2020 2020
0x20001378 : 2020 2020 2020 2020 5351 4C4E 5030 3132 SQLNP012
0x20001388 : 801A 006D 0000 0000 0000 0000 0000 0000 ...m............
0x20001398 : FFFF FD3F 0000 0000 2020 2020 2020 2020 ...?....
0x200013A8 : 2020 2034 3236 3031 42601
[cut]

2006-06-09-14.24.25.658745+120 I22946C332 LEVEL: Info
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
MESSAGE : SQO semop=> pSSemSetID:
DATA #1 : Hexdump, 4 bytes
0x20C239FC : 017C 000F .|..

2006-06-09-14.24.25.659124+120 I23279C390 LEVEL: Info
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
MESSAGE : CCI Error:
DATA #1 : Hexdump, 4 bytes
0x2FF22328 : 0000 0047 ...G

2006-06-09-14.24.25.675458+120 I23670C377 LEVEL: Info
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
RETCODE : ZRC=0x8136001C=-2127167460=SQLZ_RC_NO_CONNECTION, SQLT_SQLJC
"No connection"

2006-06-09-14.24.25.716831+120 I24048C550 LEVEL: Info
PID : 33556 TID : 1 PROC : inv_crea_dett
INSTANCE: inet NODE : 000
APPID : *LOCAL.inet.060609122424
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30
DATA #1 : String, 242 bytes
CALL STACK:
[0] 0xD1537544 sqljcReceive__FP10sqljCmnMgr + 0x158
[1] 0xD154A890 sqljrDrdaArDisconnect__FP7UCintfc + 0x1C0
[2] 0xD15751FC sqleUCdisconnect + 0x64
[3] 0xD1574EFC sqleUCtermAllCtx + 0x210
[4] 0xD1528558 sqleterm__FcP5sqlca + 0x50

Now Do you have any idea, suggest or other for to help me ?

Darn, I hoped DB2 would dump the statement text and not just the error.
I'll ask to get that fixed for the future.
Anyway... have you ever done a CLI trace? I think that's the next step.
I don't buy that what you logged is what DB2 saw.
The parser is to robust and central to be generating syntax errors for
no good reason.
Feel free to send me the code that is generating the query and the log.
Maybe I can eyeball it.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 9 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.