Intermittent sqlcode":-1042 errors from node js application

[siddhiborkar]

We are getting intermittent erros connecting to ibm cloud database from our api deployed on node js. Error:{"error":"[ibm_db] SQL_ERROR","sqlcode":-1042,"message":"[IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004\n","state":"HY000","level":"error","timestamp":"2023-02-02 09:08:45"}

We are using ibm-db node package version 2.8.0

@bimalkjha Could you please help

[bimalkjha]

@siddhiborkar If it is random issue and not reproducible always using test program, then we need db2trace for investigation. Please run below commands from terminal to collect db2trace:

db2trc on -l 512m
execute your program which causes error.
As soon as you see SQL1042C error, run below commands by stopping the application or without stopping.
db2trc dump 1.trc
db2trc off
db2trc flw 1.trc 1.flw
db2trc fmt 1.trc 1.fmt
db2trc fmt -cli 1.trc 1.cli

zip 1.flw, 1.fmt and 1.cli files and share here for investigation. Thanks.

[bimalkjha]

@siddhiborkar Let us know if you are still facing this issue. If not, then can you please close it? Thanks.

[huineng]

@bimalkjha i have the same thing on our db2 on cloud where i have 3 pods , when they start up some of them have the issue above

SQL1042C  An unexpected system error occurred.
or
SQL30081N  A communication error has been detected.

Some of these pods take like 5 seconds to connect but mostly it takes a minute an more my pool init is just 1 there is no structure on this , on local i never have any problem

only thing i can see is on local i have a mac, on cloud we use a rhel image, so impossible to trace there Any suggestions ?

[bimalkjha]

@huineng Increase pool.init to 10 and check. Thanks.

[huineng]

db2 on cloud 3 pod poolsize 10 running select 1 from sysibm.sysdummy right after

connected - ET: 26.530540402 s  no error
connected - ET: 1.4 min no errors  no error

connected - ET: 45.971154722 s  error SQL1042C An unexpected system error occurred. SQLSTATE=58004
deleted pod
connected - ET: 1.4166666666666667 min error SQL1042C An unexpected system error occurred. SQLSTATE=58004
deleted pod
connected - ET: 1.4166666666666667 min error SQL1042C An unexpected system error occurred. SQLSTATE=58004
deleted pod
connected - ET: 17.182865181 s no errors

db2 legacy 3 pods poolsize 10

connected - ET: 58.212259986 s no error
connected - ET: 13.573310261 s no errors
connected - ET: 23.488030458 s

I think i mostly definitely have an issue with the db2 on cloud instance connecting to it But queries go very fast , it's just this connecting that is cumbersome

ps , the fact that i run a query at startup is following your suggestion on an older thread where pool connections are established in parallel

thanks

[bimalkjha]

@huineng When we call pool.init(), it establishes connections with Db2 one by one in sequence and keep in the pool. pool.init() is an synchronous function. Thanks.

[huineng]

yes, that is what we discussed in the other ticket, this ticket is about the instability of connection, but particular the for the db2 on cloud it seems

[bimalkjha]

@huineng I have one Db2 on cloud instance for testing. If you could share a small test program to repro the issue, I can try to repro and check it. Also, share the OS of the system on which your client is installed and config like app is also on cloud or if we run the test program locally connecting to Db2 on Cloud then also issue is reproducible or not. The db2level output of the client. Thanks.

[huineng]

@bimalkjha thanks for your support, but i'm going to leave it here, for your info, i changed to private endpoint and that made a serious difference, so i think it all had to do with the firewalls between the clusters on openshift via bluefringe to the cloud