Tags: database, db2, identity_val_local, lastgenerated, mysql, oracle, procedure, procedures, run, select, sql, stored, sysdummy1, sysibm, ver, version

IDENTITY_VAL_LOCAL AND STORED PROCEDURES DB2 ver 7.2

On Database » DB2

2,934 words with 1 Comments; publish: Sat, 17 May 2008 22:14:00 GMT; (250109.38, « »)

If I try and do a "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1"

after I have run a stored procedure in DB2 version 7.2, I get the last

generated Key before the CallableStatement was executed in the

transaction and not the key generated in my stored procedure.

Is there a way to set the IDENTITY_VAL_LOCAL or have the stored

procedure generated key show up in my IDENTITY_VAL_LOCAL value?

Thanks Mike

Java Code:

CallableStatement proc =

((Connection)this.getConnection()).prepareCall("{call

ADMIN.PROCEDURE4(?,?,?,?,?,?,?)}");

proc.setString(1, new String("TEST"));

proc.setString(2, new String("TEST"));

proc.setString(3, new String("TEST"));

proc.setString(4, new String(""));

proc.setString(5, new String("TEST"));

proc.setInt (6, 1);

proc.setString(7, new String("TEST")); proc.executeUpdate();

System.out.println("last generated ID");

String strSql = "SELECT IDENTITY_VAL_LOCAL() FROM

SYSIBM.SYSDUMMY1" ;

Statement

stmt=((Connection)this.getConnection()).createStatement();

ResultSet result = stmt.executeQuery(strSql);

while(result.next()) {

String val = result.getString(1);

System.out.println("val = " + val);

}

stmt.close();

SQLJ stored procedure code:

package sqlj_fmb;

import java.sql.*;

import sqlj.runtime.ref.*;

public class PersonNameInsert {

public static void personNameInsert(String iFRST_NM,

String iMDL_NM,

String iLST_NM,

String iSFX_CD,

String iTP,

int iPRSN_LNK,

String iUPDT_USER

)

throws SQLException, Exception {

#sql {

INSERT INTO ADMIN.COPR_PRSN_NM

(

PRSN_NM_LNK,

FRST_NM,

MDL_NM,

LST_NM,

SFX_CD,

TP,

PRSN_LNK,

UPDT_USER,

UPDT_DTTM

)

VALUES

(

DEFAULT,

:iFRST_NM,

:iMDL_NM,

:iLST_NM,

:iSFX_CD,

:iTP,

:iPRSN_LNK,

:iUPDT_USER,

CURRENT TIMESTAMP

)

};

}

}

All Comments

Leave a comment...

  • 1 Comments
    • mike wrote:

      > If I try and do a "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1"

      > after I have run a stored procedure in DB2 version 7.2, I get the last

      > generated Key before the CallableStatement was executed in the

      > transaction and not the key generated in my stored procedure.

      > Is there a way to set the IDENTITY_VAL_LOCAL or have the stored

      > procedure generated key show up in my IDENTITY_VAL_LOCAL value?

      Interesting. That's not how identity_val_local() is meant to work.

      Arguably you could open a PMR on it (presuming you have support for V7.2.

      Cheers

      Serge

      PS: identity_val_local() is superceded by SELECT FROM INSERT in V8.1.4.

      Serge Rielau

      DB2 SQL Compiler Development

      IBM Toronto Lab

      #1; Sat, 17 May 2008 22:15:00 GMT