Error: "...SQLCODE= -540, SQLSTATE = 57001..." received when running EJPSMPRF job during migration

Technote (troubleshooting)


Problem

When performing a migration to WebSphere Portal for z/OS V7 with the DB2 Administration Tool, the migration process fails during the EJPSMPRF job, indicating an error with the xmlaccess command.

Symptom

The preExistingWebApps.xml file generated when the EJPSMPRF job runs includes an error similar to the following:

<message>com.ibm.db2.jcc.am.SqlException: [jcc][50053][12311][3.58.90] T2zOS exception: [jcc][T2zos]T2zosPreparedStatement.readPrepareDescribeOutput_:nativePrepareI
nto:1561: DB2 engine SQL error, SQLCODE = -540, SQLSTATE = 57001, error tokens = RELEASE7.APP_DESC_DD ERRORCODE=-540, SQLSTATE=57001</message>



Cause

To minimize downtime during migration, copy the source portal's JCR and Release database domains. If you use the DB2 Administration Tool to perform these copy tasks, you can encounter this problem.

Resolving the problem

To ensure that the migration process finishes successfully when using the DB2 Administration Tool, additional steps are required in the DB2 Administration Tool to ensure that the EJPSMPRF job can run successfully.


Specifically, the following changes are required for the JCR and Release tables:

  • Update the generated .ddl files for each target table and change ROWID NOT NULL GENERATED BY DEFAULT to ROWID NOT NULL GENERATED ALWAYS.
  • Update the generated LOAD statement for each target table to ignore the ROWID field.
  • Run the copy utility to make the target tables accessible.

The steps below provide an example of how to use the DB2 Administration Tool to migrate the source table, SYSADM.XYZ, to the target table, ADMF001.XYZ. Source table SYSADM.XYZ contains two rows of data and the ROWID is set to GENERATED ALWAYS by DB2.

1. Use the MIG function of the DB2 Administration Tool to migrate the source table to target table. The tool generates SYSADM.MIGXYZ01.JCL.

2. Submit the ADBMGS0 job. This creates two more jobs in the SYSADM.MIGXYZ01.JCL, one for source and one for target.

3. Submit the ADBMGS1 job to create additional datasets.

4. Edit the SYSADM.MIGXYZ01.DDL file.

5. Locate the target table and modify the ROWID.
    Example with original ROWID :

    CREATE TABLE XYZ
     (C1                CHAR(2) FOR SBCS DATA WITH DEFAULT NULL,
      XYZID             ROWID NOT NULL GENERATED BY DEFAULT,
      C3                INTEGER WITH DEFAULT NULL,
      C4                VARCHAR(2) FOR SBCS DATA WITH DEFAULT NULL)


    Example with updated ROWID :

    CREATE TABLE XYZ
     (C1                CHAR(2) FOR SBCS DATA WITH DEFAULT NULL,
      XYZID             ROWID NOT NULL GENERATED ALWAYS,
      C3                INTEGER WITH DEFAULT NULL,
      C4                VARCHAR(2) FOR SBCS DATA WITH DEFAULT NULL)

6. Edit the SYSADM.MIGXYZ01.CNC.s1 dataset and update the LOAD statement with the IGNOREFIELDS keyword.
    Example with original LOAD statement:

    LOAD DATA INDDN SYSREC   LOG NO
    EBCDIC  CCSID(00037,00000,00000)
     RESUME YES
     ENFORCE NO
     SORTNUM 4
     SORTDEVT SYSDA
    INTO TABLE
    ADMF001.XYZ
    WHEN(00001:00002) = X'0003'
    ...


    Example with updated LOAD statement:

    LOAD DATA INDDN SYSREC   LOG NO
    EBCDIC  CCSID(00037,00000,00000)
     RESUME YES
     ENFORCE NO
     SORTNUM 4
     SORTDEVT SYSDA
    INTO TABLE
    ADMF001.XYZ
      IGNOREFIELDS YES
    WHEN(00001:00002) = X'0003'
    ...

7. From the SYSADM.MIGXYZ01.JCL, submit the ADBMGT1 job. The target table is created and populated with the data.

8. Run the DLL command to check the DLL for ADMF001. Verify that the ROWID is defined as ROWID NOT NULL GENERATED ALWAYS.

9. Run the COPY utility on the tablespace for the target table.

10. Examine the contents of the target table, ADMF001.XYZ, and verify that the ROWID is now different from that of the source table, SYSADM.XYZ.


Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

WebSphere Portal
Migration

Software version:

7.0

Operating system(s):

z/OS

Software edition:

Enable

Reference #:

1441277

Modified date:

2011-01-26

Translate my page

Machine Translation

Content navigation