IBM Support

IC71820: RESTORE TRANSPORT SCHEMA FAILS WITH SQL0010N

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Restore transport schema might fail with SQL0010N
    
    1. The command fails as follows:
    db2 "restore database prod_db tablespace(tbsp1,tbsp2) schema
    (test) from /backups transport stage in stage_db into test_db"
    SQL0010N  The string constant beginning with "'" does not have
    an ending
    string delimiter.  SQLSTATE=42603
    
    
    2.
    db2diag.log will show the following entries
    
    FUNCTION: DB2 UDB, database application extension for utili,
    transport_readDDLfromFile, probe:7296
    DATA #1 : String, 50 bytes
    Transport:Unexpected error during replay DDL stmt.
    DATA #2 : String, 251 bytes
    
    UPDATE SYSSTAT.COLUMNS
    SET COLCARD=36,
        NUMNULLS=0,
        SUB_COUNT=-1,
        SUB_DELIM_LENGTH=-1,
        AVGCOLLENCHAR=-1,
        HIGH2KEY='.',
        LOW2KEY=X'14',
        AVGCOLLEN=2
    WHERE COLNAME = 'FIELDTYPE' AND TABNAME = 'TAB1' AND TABSCHEMA =
    'TEST '
    DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -10   sqlerrml: 1
     sqlerrmc: '
     sqlerrp : SQL09072
     sqlerrd : (1) 0x00000000      (2) 0x00000000      (3)
    0x00000000
               (4) 0x00000000      (5) 0x00000000      (6)
    0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
    
               (7)      (8)      (9)      (10)        (11)
     sqlstate: 4260
    
    
    The failure is due to an incorrect character introduced in the
    transportschemaddl.out which depends on the LC_CTYPE settings on
    the environment where we run the restore commnad.
    
    a)
    Check in the staging database the file transportschemaddl.out
    and find the sql shown in the db2diag.log.
    Try to run this statement from CLP and see if this fails.
    
    b)
    If the statement fails or if it clearly has incorrect
    characters, try setting LC_CTYPE to a different value.
    Before testing the restore command, the output generated by
    db2look should show correct values on tab1.out below.
    
    db2look -d prod_db -m -l -a -e -z test -t tab1 -o tab1.out
    
    Example:
    
    UPDATE SYSSTAT.COLUMNS
    SET COLCARD=36,
        NUMNULLS=0,
        SUB_COUNT=-1,
        SUB_DELIM_LENGTH=-1,
        AVGCOLLENCHAR=-1,
        HIGH2KEY=X'E8',
        LOW2KEY=X'14',
        AVGCOLLEN=2
    WHERE COLNAME = 'FIELDTYPE' AND TABNAME = 'TAB1' AND TABSCHEMA =
    'TEST ';
    

Local fix

  • Check settings of LC_CTYPE as db2look is using isalnum calls.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Restore transport makes use of db2look to generate the ddl   *
    * to                                                           *
    * replay on the target database, if this ddl is incorrect due  *
    * to                                                           *
    * locale settings the restore transport will fail.             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7 and Fix pack 4                    *
    ****************************************************************
    

Problem conclusion

  • The problem was first fixed in DB2 version 9.7 and Fix pack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC71820

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-10-11

  • Closed date

    2011-04-28

  • Last modified date

    2011-04-28

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC72043 IC72044 IC72781

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC71820

Modified date: 28 April 2011