Prev: SQL1224N - A database agent could not be started to service a request
Next: No remote connections available
From: eap90210 on 8 Jun 2006 09:51
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_LOC_LOCALITA_1,NVD_UBI_UBICAZIONE_1,NVD_PRIMO_LIVELLO_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_CODICE_TER
ZO,NVD_RDA,NVD_SERIAL_NUMBER_1,NVD_QUANTITA_1,NVD_GST_PROGRESSIVO_INTERNO,NVD_ORDINE_USCITA_1,NVD_RIGA_ORDINE_USCITA_1,NVD_ETICHETTA_1,NVD_GERARCHIA,NVD_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_LIVELLO,DWH_MAG_TERZO_LIVELLO,DWH_MAG_QUARTO_LIVELLO,DWH_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_QUANTITA,DWH_PROGRESSIVO_INTERNO,DWH_NUMERO_ORDINE,DWH_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


From: Serge Rielau on 8 Jun 2006 10:58
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/ondemandbusiness/conf2006/
From: eap90210 on 8 Jun 2006 12:09
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" <srielau(a)ca.ibm.com> ha scritto nel messaggio
news:4eqs73F1fuqctU1(a)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/ondemandbusiness/conf2006/


From: Serge Rielau on 8 Jun 2006 12:24
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" <srielau(a)ca.ibm.com> ha scritto nel messaggio
> news:4eqs73F1fuqctU1(a)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/ondemandbusiness/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/ondemandbusiness/conf2006/
From: eap90210 on 9 Jun 2006 08:35
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" <srielau(a)ca.ibm.com> ha scritto nel messaggio
news:4er18sF1fsvb0U1(a)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" <srielau(a)ca.ibm.com> ha scritto nel messaggio
>> news:4eqs73F1fuqctU1(a)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/ondemandbusiness/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/ondemandbusiness/conf2006/


 |  Next  |  Last
Pages: 1 2
Prev: SQL1224N - A database agent could not be started to service a request
Next: No remote connections available