Hi,

the result of SQL-Prepare-Statement in a Cobolprogram is -104, when i bind this program with option ENCODING(UNICODE).
SQL-prepare is successful, binding  the program with ENCODING(1141/273) .

Are there any further options to specify?

Environment:
Rational Business Developer 9.0.1.1
Cobol (z/Os) V5.1

Client Codepage = 1252, Server Codepage = 273

thanks
thomas

Thomas,

You can attach the cbl_result.xml and ccubldc.log

Hi Hsieh,

attached file GenWT06B!

Hi Thomas,

If your program has Dynamic SQL statement then the SQL syntax will not be checked in BIND and it was successful.

However, if your Dynamic SQL statement has syntax error then the error will only be detected at runtime.

I believe that your SQL Statement is invalid in EGL.  I could not get the SQL statement through the COBOL source to find out where this error.

Hsieh

It looks like the program is building its own sql statement and then executing a prepare on it. The sqlexception record might have more info.

it might be useful to use writestdout against the variable holding the prepared statement and see what it looks like. The -104 error code indicates an invalid sql token in the statement.

Another thing to consider is this. Even though the program is using unicode for the prepared statement, when it gets processed and given to the exec sql statement, the unicode is converted to PIC X (characters). You can see it in the following code:

           MOVE FUNCTION DISPLAY-OF(EZETYPE-DATA IN EZETYPE-STRING0 (1: 
           EZETYPE-LENGTH IN EZETYPE-STRING0)) TO EZESQL-PREPARE-DATA 
           (1: EZESQL-PREPARE-LENGTH)
           EXEC SQL
                PREPARE EZEPREPARE2 FROM 
           :EZESQL-PREPARE
           END-EXEC

https://www-304.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n104.dita

attached file wt06b.egl contains the sample program.

writestout after execution is:

BIND ENCODING(273)

I003WT01 20150702083632 prepare1 succesful
I003WT01 20150702083632 prepare2 succesful

BIND ENCODING(UNICODE) content of SQL-Exception-Record

I003WT01 20150702085158 SQLCOMMAND(PREPARE ) SQLCODE(-00104) SQLMSG=..<ERR_STMT> <WNG_STMT> GET SQL SAVEPOINT HOLD FREE ASSOCIATE CALL  
Im Programm WT06B ist bei der Funktion PREP1 ein Fehler aufgetreten. Im Programm WT06B ist bei Anweisungsnummer 22 ein Fehler aufgetreten. SQL-Ausnahmebedingung.                                                                                                   
I003WT01 20150702085158 SQLCOMMAND(PREPARE ) SQLCODE(-00104) SQLMSG=..<ERR_STMT> <WNG_STMT> GET SQL SAVEPOINT HOLD FREE ASSOCIATE CALL
Im Programm WT06B ist bei der Funktion PREP2 ein Fehler aufgetreten. Im Programm WT06B ist bei Anweisungsnummer 38 ein Fehler aufgetreten. SQL-Ausnahmebedingung.                                                                                                  

You might need to turn on some type of SQL tracing, or try to run that sql command from a sql utility, but it should give additional information about the parm it is complaining about.

the -104 means that the stmt is invalid

the (simple) stmt is basically ok, but when the program is bound UNICODE DB2 converts the statement in a way that it cannot be prepared.

APPTUNE SQL ERROR ANALYSIS shows following:

   STATEMENT TYPE...... DYNAMIC              STATEMENT NUMBER.     2965       

   CALL TYPE........... PREPARE              SECTION NUMBER...        3       
                                                                              
     DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ".". SOME SYMBOLS THAT   
              MIGHT BE LEGAL ARE: <ERR_STMT> <WNG_STMT> GET SQL SAVEPOINT     
              HOLD FREE ASSOCIATE CALL                                        
     DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                         
     DSNT415I SQLERRP    = DSNHLEX SQL PROCEDURE DETECTING ERROR              
     DSNT416I SQLERRD    = 17  0  0  -1  1  502 SQL DIAGNOSTIC INFORMATION    
     DSNT416I SQLERRD    = X'00000011'  X'00000000'  X'00000000'  X'FFFFFFFF'
               X'00000001'  X'000001F6' SQL DIAGNOSTIC INFORMATION            
     DSNT417I SQLWARN0-5 = W,,,,, SQL WARNINGS                                
     DSNT417I SQLWARN6-A = ,,W,,   SQL WARNINGS                               
   ------------------------------- SQL TEXT ----------------------------      
    Select   filialnummer3                                                  
    from     vamaprae                                                       

To let others know the outcome on this.

After deeper investigation through a PMR, it was found that EGL was using the PIC X (char) datatype for the representation of the statement being prepared.  To resolve, we have taken an APAR which will introduce a new symbolic parameter that can be used at generation time telling the generator to use the appropriate COBOL Datatype (PIC N, PIC G, or PIC X) so that a Unicode encoding on the bind will work.

The APAR number is PI45080 and will be available in any future shipments for V9.1 or greater.

If a fixtest is required for V9.x, please open a PMR and request a testfix.

Mark