SQLSTATE '08003' in SQLRPGLE Program IBM

Question

I have uncounted a problem in an SQLRPGLE program. It is used as an API program to send mails to users when something is wrong with an order.

To sketch the workings of the program, we have an autostart job that start a program (program A) that look at every order created to see if all the data in it is ok. If not it calls a mail API program.

In Program A there are several API calls to the send mail. In the API mail program we do an SQL insert in a table called FCMAILS (as logging).

In program A only 1 call always goes wrong. This results after some testing in a SQLSTATE 08003. Meaning 'application process not in a connected state'. The program does not crash, or give any sysopr notification. It also does not write a record in the file FCMAILS.

Does anybody has an idea how to solve this problem? I have been looking at a the SQLCONNECT command for AS400 but don't find a lot of info over it and I don't want to make it worse than it already is.

/free
     // save body to text file for loading into clob
     BodyObj_Data = Body;
     BodyObj_Len = %len(Body);
     callp GenGuid(GUID);

     exec sql
       insert into fcmails(
          Guid,SendUser,Program,Sender,Recipient,RecipientCc,RecipientBcc,
          Subject,Body) values
          (:GUID,:SendUser,:Program,:Sender,:Recipient,:RecipientCc,
            :RecipientBcc,:Subject,:BodyObj);

     if sqlstate <> '00000';
       callp XSndMsg(' ':'SYSOP':'Mail Error: P:' + Program +' S:'+sqlstate+
                                 ' subj:' +Subject);

     endif;
     // trigger mail program
     callp SndDtaQ('APIMAILDTQ':'FCDTA500':%Size(GUID):
                    GUID);

     return *on;
  /end-free
 PapiCreateMail    E

score 1 · Answer 1 · answered Dec 13 '19 at 13:43

1

I have seen that in some very specific instances, though I am not sure why it happens as I am never connected to anything but the local database.

What I did was create a procedure that reconnects using EXEC SQL CONNECT RESET;. I call that procedure whenever the 08003 SQLSTATE is found. Then I can retry the statement that failed. It takes some extra looping, and checking to prevent an infinite loop, but I never had a problem with it after that.

answered Dec 13 '19 at 13:43

jmarkmurphy

11,030
31
59

i have the Connect reset now implemented when the sqlstate = 08003 so lets hope this works. i also find it strange that the connection would get lost, FCMAILS is a table on the local DB2 database. lets hope this is an improvement. – WouterV Dec 17 '19 at 08:37

score 0 · Answer 2 · answered Jan 08 '20 at 07:36

after a long time waiting (it's a automatic process that checks incoming orders from customers) it happened again.

this is the code that i have right now, i check 1 time if the connection is closed by looking at sqlstate 08003. then reconnecting and doing the same sql

exec sql
       insert into fcmails(
          Guid,SendUser,Program,Sender,Recipient,RecipientCc,RecipientBcc,
          Subject,Body) values
          (:GUID,:SendUser,:Program,:Sender,:Recipient,:RecipientCc,
            :RecipientBcc,:Subject,:BodyObj);

     if sqlstate = '08003';
       EXEC SQL CONNECT RESET;
       //retry same command
       exec sql
          insert into fcmails(
          Guid,SendUser,Program,Sender,Recipient,RecipientCc,RecipientBcc,
          Subject,Body) values
          (:GUID,:SendUser,:Program,:Sender,:Recipient,:RecipientCc,
            :RecipientBcc,:Subject,:BodyObj);
     ENDIF;

     if sqlstate <> '00000';
       callp XSndMsg(' ':'SYSOP':'Mail Error: P:'+Program+
                         ' S:'+sqlstate+' subj:'+Subject);
     endif;

     // trigger mail program
     callp SndDtaQ('APIMAILDTQ':'FCDTA500':%Size(GUID):
                    GUID);

any more idea's i can do a loop to check 10 times, if that would be better?

SQLSTATE '08003' in SQLRPGLE Program IBM

2 Answers2