IBM Support

-818 error calling an external embedded SQL Stored Procedure or UDF with KEEPFENCED=YES

Troubleshooting


Problem

-818 error calling an external embedded SQL Stored Procedure or UDF with KEEPFENCED=YES

Symptom

By definition, a -818 means the following:
SQL0818N A timestamp conflict occurred.

This error occurs when the consistency token (timestamp) of the compiled library does not match the consistency token of the package in the database. The database package is created by binding the bind file created during prep.

In some cases, even if the bind file is rebound to the database and the consistency token between what is found in system catalogs and the compiled library matches, a -818 error may still occur.

Cause

This typically happens when KEEPFENCED=YES is set in the database manager configuration. When this setting is enabled, libraries are cached by the db2fmp process and the cached library may be an older library which has a consistency token which is different than the new consistency token of the bind file and/or library

Environment

DB2 LUW environments such that:

  • External Embedded SQL Stored Procedures or UDFs (C, C++, COBOL) are being developed and executed.
  • The KEEPFENCED=YES parameter is set in the database manager configuration.
  • Usually occurs during development stages when the library may be changing frequently.

Diagnosing The Problem

Verify if KEEPFENCED=YES is set in the database manager configuration:
>db2 get dbm cfg
Keep fenced process (KEEPFENCED) = YES

The following commands can be used to verify what the value of the consistency tokens are. This is an example for the $INSTHOME/sqllib/bnd/db2schema.bnd file

  • Check the consistency token found in the bind file.

    Run the following command. The value of AAAAAZIZ is the consistency token.

    db2bfd -b db2schema.bnd
    Creator "NULLID "
    App Name "SQLL9H1L"
    Timestamp "AAAAAZIZ:2009/08/25 00:00:00:00"
  • Check the consistency token found in the system catalogs.

    1) Verify if what codepage the database is:
    $ db2 get db cfg for <database>
    Database code page = 1208
    Database code set = UTF-8
    Database country/region code = 1

    2) If it is a unicode databse like UTF-8, then run:
    db2 "select cast(unique_id as char(8) for mixed data) from syscat.packages where pkgschema='NULLID' and pkgname='SQLL9H1L'"

    3) If its a non-unicode database like code page 819, then run:
    db2 "select cast(unique_id as char(8) for sbcs data) from syscat.packages where pkgschema='NULLID' and pkgname='SQLL9H1L'"

    4) The query should return a result such as the following:
    1
    --------
    AAAAAZIZ
    1 record(s) selected.
  • Check the consistency token that is being requested by the external routine call.

    Take a DRDA trace of the -818 error message and look for the consistency token in the trace:

    db2trc on -f trace.dmp
    <reproduce the problem>
    db2trc off
    db2trc fmt trace.dmp trace.fmtc -c

    Within the trace.fmtc file, the consistency token is found in the PKGCNSTKN codepoint just prior to when the -818 error occurs in the trace.
    NM: PKGNAMCSN - RDB Pkg Name, Const Tkn & Sec Num
    LL: 68 CP: 2113
    * RDBNAM - Relational Database Name
    ASCII: TEST1
    EBCDIC: ..................
    * RDBCOLID - RDB Collection Identifier
    ASCII: NULLID
    EBCDIC: +.<<..............
    * PKGID - RDB Package Identifier
    ASCII: SQLL9H1L
    EBCDIC: ..<<...<..........
    * PKGCNSTKN - RDB Package Consistency Token
    DATA: (ASCII) (EBCDIC)
    41414141415A495A AAAAAZIZ .....].]
    * PKGSN - RDB Package Section Number
    DECIMAL: 54
    HEXADECIMAL: 0036
  • Check the consistency token from the compiled library.

    On AIX, use the "strings" command. As seen in the example output below, the consistency token of AAAAAZIZ is found within a substring of the output.

    $ strings db2schema | more
    SQLARTIN

    ¬EANAIAAAAAAZIZ01111 2
    NULLID
    SQLL9H1L
    restricted
  • Consistency token can be converted into pre-compilation date
This helps with troubleshooting to determine when the consistency token was created to see if it matches pre-compilation date of the application.

$ ktimestamp.pl
Usage: ktimestamp <consistency-token>

$ ktimestamp.pl QBjMNMBh
QBjMNMBh = 2017/01/12 13:12:35:078

ktimestamp.plktimestamp.pl

Resolving The Problem

If any of the consistency tokens found within the bind file, system catalogs, DRDA trace, or the compiled library do not match, the first step would be to use the BIND command to the bind file that was created during the PREP of the library. A basic BIND command would be "db2 bind db2schema.bnd"

If this does not resolve the -818 error, then it is likely that the library is being cached because of the KEEPFENCED=YES setting. Commonly it will be observed that the db2trc will show a different (likely older) consistency token than the consistency tokens seen in the bind file, system catalogs, DRDA trace, or compiled library.

If this is the case, restarting the instance (db2stop, db2start) will allow the routine cache to be cleared and the new library will be used instead via the creation of a new db2fmp process. Since the new library will be used that has the same consistency token of the package that was bound, the -818 error will not occur.

As a general rule, set KEEPFENCED=NO and restart the instance. Leave this configuration until development is complete and there will be no more library changes.
1. db2 update dbm cfg using keepfenced no
2. db2stop
3. db2start

Once the libraries that will be used for long term are on the system, change the setting to KEEPFENCED=YES and restart the instance.
1. db2 update dbm cfg using keepfenced yes
2. db2stop
3. db2start

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP & UDF) - SQL","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1;10.1;10.5;11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21448684