SQL error -904 accessing : [IBM][CLI Driver][DB2] DSNT408I SQLCODE = -904

[Former Member]

Dear all,

we are importing a Basis queue from SAPKB620053 to SAPKB620063 but the importing job dead (RC 12) with the following reason:

Start import R3TRFUGRVSCAN_RFC ...

sap_dext called with msgnr 4:

db call info

function: db_docu

fcode: CI_WRITE_LINE

len: 68

key: FUVSCAN_RFC_INIT DT0002

ok fetches: 0

retcode: 4

SQL error -904 accessing : [IBM][CLI Driver][DB2] DSNT408I SQLCODE = -904, ER

RESOURCE 00000304, AND RESOURCE NAME SX40XAAA.DOKCLU .X'01815E' '.X'03'

DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 102 13172746 0 13813475 -1010298874 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000066' X'00C9000A' X'00000000' X'00D2C6E3' X'C3C81006' X'00000000' SQL DIAGNOSTIC

INFORMATION

Main import

End date and time : 20071204172624

Ended with return code: ===> 12 <===

*******************************************************

Kernel release 640

Compilation Linux GNU SLES-8

Patch Level 175

ABAP Load 1521

CUA load 16

Mode opt

Rsyn file

*******************************************************

Operating system: Linux Database system DB2/zOS

Machine type: s390x Release 8.1.5

*******************************************************

For note 875750, our DB2 version is 8 and in the note is reported the

following:

>> Mandatory: Your SAP system runs on DB2 V7. If it runs on DB2 V6 or

V8, this note does not apply!!!

For note 565490: we have checked the environment and update the

variables.

For note 355213: the parameters LOCKMAX = 1.000.000 and NUMKUSR =

2.097.152 (The maximum Value) >> The system parameter NUMLKTS sets the

default for the tablespace attribute LOCKMAX. . If the number of locks

on a particular tablespace exceeds the LOCKMAX value, these locks will

be replaced by a single tablespace-scope lock (note that the LOCKMAX is

enforced on a per thread, per tablespace basis).

We have the following parameters:

NUMKUSR = 2.097.152 (The maximum Value)

NUMLKTS= 10000

LOCKMAX = 1000000 for the TS

DSNT501I -DGQF DSNILMCL RESOURCE UNAVAILABLE 015

CORRELATION-ID=GQFGEN000

CONNECTION-ID=SERVER

LUW-ID=GA30243A.EB9D.071202215036=251705

REASON 00C90096

TYPE 00000304

NAME SX40XAAA.DOKCLU .X'01815D' '.X'17

Anybody can help me to go on with the import queue?

Kind Regards.

[thomas_vogt · Accepted solution]

Nicola,

You already have opened a problem ticket for this. Please open the service connection and request the ticket to be forwarded to development support.

Regards,

Thomas

[Former Member · Accepted solution]

Thomas,

during the queue

SAPKH47023 SP 23 for SAP_APPL 4.70

SAPKH47024 SP 24 for SAP_APPL 4.70

SAPKIPUL08 IS-U/CCS 472: patch 0008, CRT for SAPKH47023-24

SAPKH47025 SP 25 for SAP_APPL 4.70

SAPKIPUL09 IS-U/CCS 472: patch 0009

SAPKIPUL10 IS-U/CCS 472: patch 0010, CRT for SAPKH47025

**********************************************************************************************

4 ETW000 *** ERROR => DB2 Call 'SQLExecute' Error: sqlcode = -904 : [CLI Driver][DB2] DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION

CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096, TYPE OF

4 ETW000 RESOURCE 00000F01, AND RESOURCE NAME 030A.0005.54D8B923A053D324210401513D80.00002

4 ETW000 DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE

4 ETW000 DSNT415I SQLERRP = DSNXRUID SQL PROCEDURE DETECTING ERROR

4 ETW000 DSNT4 36 727.924408

4 ETW000 *** ERROR => DB2--904 occured; SQL statement is ==>

4 ETW000 20 727.924428

4 ETW000 UPDATE "REPOSRC" SET "DATA" = ? WHERE "PROGNAME" = ? AND "R3STATE" = ? ;

4 ETW000 15 727.924443

4 ETW000 ***LOG BY4=>sql error -904 performing CLC on table REPOSRC

4 ETW000 28 727.924471

4 ETW000 ***LOG BY0=>[IBM][CLI Driver][DB2] DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON

00C90096, TYPE OF

4 ETW000 RESOURCE 00000F01, AND RESOURCE NAME 030A.0005.54D8B923A053D324210401513D80.00002

4 ETW000 DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE

4 ETW000 DSNT415I SQLERRP = DSNXRUID SQL PROCEDURE DETECTING ERROR

4 ETW000 DSNT416I SQLERRD = -150 13172746 0 13226969 -975761407 0 SQL DIAGNOSTIC INFORMATION

4 ETW000 DSNT416I SQLERRD = X'FFFFFF6A' X'00C9000A' X'00000000' X'00C9D3D9' X'C5D71001' X'00000000' SQL DIAGNOSTIC

4 ETW000 INFORMATION

4 ETW000 ð 55 727.924526

2EETW000 sap_dext called with msgnr "1":

2EETW000 -

-

---

db call info -

-

---

2EETW000 function: db_report_interface

2EETW000 fcode: CLOSE_LINE_MODE

2EETW000 tabname: SOURCE

2EETW000 len: 40

2EETW000 key: LJ1BVU27

2EETW000 retcode: 1

2EETW125 SQL error "-904" during "" access: "[IBM][CLI Driver][DB2] DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON

00C90096, TYPE OF

3WETP000 RESOURCE 00000F01, AND RESOURCE NAME 030A.0005.54D8B923A053D324210401513D80.00002

3WETP000 DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE

3WETP000 DSNT415I SQLERRP = DSNXRUID SQL PROCEDURE DETECTING ERROR

3WETP000 DSNT416I SQLERRD = -150 13172746 0 13226969 -975761407 0 SQL DIAGNOSTIC INFORMATION

3WETP000 DSNT416I SQLERRD = X'FFFFFF6A' X'00C9000A' X'00000000' X'00C9D3D9' X'C5D71001' X'00000000' SQL DIAGNOSTIC

3WETP000 INFORMATION

3WETP000 ð"

4 ETW000 ROLLBACK (66849820).

1 ETP154 MAIN IMPORT

1 ETP110 end date and time : "20071205131622"

1 ETP111 exit code : "12"

1 ETP199 ######################################

***********************************************************************************************

The message has been updated and the connection has been opened.

[thomas_vogt · Accepted solution]

Nicola,

For any reason the ticket has been sent to you, sorry for this. Please send the ticket back to SAP and request the ticket to be forwarded to development support.

Regards,

Thomas

[thomas_vogt · Accepted solution]

Nicola,

PTFs mentioned below are missing:

PK40834: MSGDSNT501I RC00C90096 NUMLKUS EXCEEDED FOR LOB TABLESPACE.

PK41812: SQLCODE904N RC00C90096 NUMLKUS REACHED WITH PK19653/UK16666

The ticket has been updated with the details.

Regards,

Thomas

[Former Member · Accepted solution]

Hello Nicola,

Someone before me has mentioned the NUMLKTS and NUMLKUS parameters in the DSNTIJUZ module.

These parameters come by default with very low values compared to what an SAP system needs to function properly, therefore you have to tailor them to SAP's needs, usually 2000000 for each, which limits the total number of locks on a tablespace to 2 million, and the same for the total number of locks per user.

Of course, in many case this amount of locks will result in escalation, therefore the limit will not be actually reached.

Best regards,

Daniel

[Former Member]

Remove and Create DB index helped me.

[Former Member]

Remove and Create DB index helped me.

[Former Member]

Nicola,

We have seen this issue several times, especially when performing installs and upgrades. First of, we have altered all our tables LOCKMAX to SYSTEM so that they will escalate according to NUMLKTS. In your case, 1000000 for the LOCKMAX overrides the NUMLKTS of 10000.

Then, we lower NUMLKTS so that escalations take place much sooner, thus avoiding the 00C90096 message since you will be acquiring fewer locks even though they are tablespace locks. However, in your case, NUMLKTS is already low at 10000. Normally, SAP recommends this be around 1000000 when running a production system.

Once you alter the LOCKMAX to SYSTEM, try re-running your process. Keep in mind that escalating sooner may shut out other users so you may want to do this when the system is quiet. If you are successful, then you may want to increase your NUMLKTS to 1000000.

Hope this helps.

Regards,

Bob Di Filippo