Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Unanswered: IMPORT: can't define MESSAGES file

    DB2 9.7 LUW.

    I have a stored proc that does multiple import statements that import data from CSV files into tables. When I run the proc, the statements in the beginning of the proc work fine. This import statement in the middle...

    Code:
    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /home/db2inst1/SRS2013IMPORT/DataValueType.csv OF DEL modified by coldel; METHOD P (1, 2, 3, 4, 5, 6, 7) SKIPCOUNT 1 INSERT INTO SRS_IMPORT.DATAVALUETYPE_IMPORT(DataValueTypeId, DataValueTypeNK, DataValueTypeName, UnitNK, DataCategoryName, AggregationMethodName, IsLocked)');
    ... seems to import its data correctly but still gives a warning,

    IWAQ0003W SQL warnings were found
    SQLState= There is at least one warning message in the message file.. SQLCODE=3107, SQLSTATE= , DRIVER=4.14.113
    This warning makes the proc ignore the rest of the import statements.

    In order fix the above, I need to read what the message file says so to I tried to add a MESSAGES file to the command:

    Code:
    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /home/db2inst1/SRS2013IMPORT/DataValueType.csv OF DEL modified by coldel; METHOD P (1, 2, 3, 4, 5, 6, 7) SKIPCOUNT 1 MESSAGES import.msg INSERT INTO SRS_IMPORT.DATAVALUETYPE_IMPORT(DataValueTypeId, DataValueTypeNK, DataValueTypeName, UnitNK, DataCategoryName, AggregationMethodName, IsLocked)');
    But then db2 complains:

    An unexpected token "import.msg" was found following "MESSAGES". Expected tokens may include: "ON".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.14.113
    I have tried with
    MESSAGES import.msg
    MESSAGES "import.msg"
    MESSAGES \"import.msg\"

    but always get the same "unexpected token" error. What should I do to be able to know what DB2 is warning me about in the import - or is there some way that I can get the rest of the import operations to run regardless of the warning?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would suggest that you give the full path name of your message file.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2013
    Posts
    4
    Like /home/<username>/import.msg instead of just import.msg? It doesn't make any difference, I still get the "unexpected token" error.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The message file handling may be somewhat different when IMPORT is used within the SYSPROC.ADMIN_CMD Stored Proc:
    IMPORT using ADMIN_CMD - IBM DB2 9.7 for Linux, UNIX, and Windows
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2013
    Posts
    4
    Thanks!

    I needed to use MESSAGES ON SERVER (a special syntax that needs to be used when running IMPORT from within a SYSPROC.ADMIN_CMD) and then run the SQL given in the MSG_RETRIEVAL column in the returned result set that is returned from the IMPORT command.

    The SQL looked like
    SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('857832809_1572825553 _DB2INST1')) AS MSG

    And the messages looked like
    "The character data in row "2" and column "7" was truncated because
    the data is longer than the target database column."

    This was strange, as the column contained 1 character (1 or 0) and the database column was 1 character. Then I realized that it was the last column in the CSV file and I had to change my Windows line endings (CR LF) to UNIX (LF) ones in order to not include an invisible CR character in the imported data.
    Last edited by maccer83; 07-28-13 at 11:45.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •