Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2012
    Posts
    70

    Unanswered: load SQL3229W reason code 1

    Hello

    I have to migrate a DB2 v9.1 database from AIX 5.3 server to a DB2 v10.5 database under LINUX

    I have difficulties with one table which contains LOBS. I unloaded the data with HP-Unload and transfered it to the LINUX server to load it in the new database.
    The load failed for some rows (not all of them) with the error SQL3229W reason code 1 (The file named in the row and column cannot be found.)

    I don't understand what happened

    Has someone an idea?

    Thanks in advance
    best regards
    Renaud

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    could you share the complete unload/load command and error
    does the message contain the row number - in that case the data can be checked/verified against what happened
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2012
    Posts
    70
    unload command (HPU Version: 4.1.2)
    ----------------
    db2hpu -f HPU-controlfile -i <instance> -m <logfile>


    HPU-controlfile:
    ----------------

    GLOBAL CONNECT TO VWSABNA DB2 NO OPTIONS DOUBLE DELIM ON UMASK "022";

    UNLOAD tablespace
    TS_MLPSN
    LOCK YES
    QUIESCE YES
    LLS ON
    OUTPUT (
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/DATEN_"
    )
    LOB IN (
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_00",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_01",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_02",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_03",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_04",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_05",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_06",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_07",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_08",
    "/tempvol/vwsacc9/UNLOAD_MIGRATION/VWSABNA/TS_MLPSN/LOB_09")

    LOBFILE ("TS_MLPSN_")
    CCSID(923)
    FORMAT IXF
    ;



    load SQL-Script
    ---------------

    load from
    /transfer_db2/VWSABNA/TS_MLPSN/DATEN__TS_MLPSN_MLPSN_SNT_ATTACH
    of ixf
    lobs from
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_00,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_01,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_02,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_03,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_04,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_05,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_06,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_07,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_08,
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_09
    insert into MLPSN.SBT_ATTACH nonrecoverable;


    Thanks
    Renaud

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think the error message: "The file named in the row and column cannot be found" -- is self-explanatory. Check that the paths in LOB Location Specifiers in the IXF file are available on the target server.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2012
    Posts
    70
    Hi
    thanks for the clue, but I already checked the paths and the files on server, and all are available..

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    In the "LOBS FROM" clause , try appending a "/" to the path names...

  7. #7
    Join Date
    Aug 2012
    Posts
    70
    unfortunately the same error occurs again...

    for the migration I had to transfer the files from a server to the other server.
    Maybe a file is corrupt. Is there a possibility to check the consistence of the files?

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    When the "Lobs from" path terminating in a "/", what details are in the db2diag when you try the load for this table? (if necessary temporarily set diaglevel 4 immediate, then do the load of this table and set it back to 3 afterwards).

    Can you demonstrate that the user-account performing the load can open the absolute path and files in (for example: /transfer_db2/VWSABNA/TS_MLPSN/LOB_00/* ) ?

    Use strings (or whatever) to view some of the LLS in your IXF file?

    Do you know how many rows are in the table? if there are very few, does IMPORT show any difference?

  9. #9
    Join Date
    Aug 2012
    Posts
    70
    since there are other databases in the instance I first have to clarify whether I can modify the diaglevel.

    I can access the LOBs-files and view with strings

    Unfortunately the table is not small and has 540,000 rows in 1400 Pages (16KB)

  10. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    540,000 rows seems ver small to me.

    Only for problem-determination: You could try an import deliberately using ROWCOUNT 10 (to try inserting only the first 10 rows from the ixf file) , to see if you get any more or different information, and you can delete the rows later when you find the issue.

    There might be interesting messages in db2diag even at diaglevel 3 (i.e. 4 might not be essential).

  11. #11
    Join Date
    Aug 2012
    Posts
    70
    The import failed with following information:

    SQL3040N The utility cannot use the file name
    "/transfer_db2/VWSABNA/TS_MLPSN/LOB_07/TS_MLPSN__MLPSN_SNT_ATTACH" specified
    in the "LLS" parameter. Reason code: "6".

    db2 ? SQL3040N
    ------------------
    SQL3040N The utility cannot use the file name "<file-name>" specified
    in the "<option-name>" parameter. Reason code: "<reason-code>".

    6. The sum of the starting position and the length of the input
    data exceeds the size of the file with file name "<file-name>".


    db2diag.log (level 3)
    ---------------------
    2014-08-26-15.00.23.360889+120 I65975542E735 LEVEL: Error (Origin)
    PID : 968 TID : 140393596852000 PROC : db2bp
    INSTANCE: lfprd NODE : 000 DB : VWSPROD
    APPID : *LOCAL.lfprd.140902133125
    HOSTNAME: hle-prod-db01
    FUNCTION: DB2 UDB, database utilities, sqluiFindFile, probe:5726
    MESSAGE : ZRC=0xFFFFF420=-3040
    SQL3040N The utility cannot use the file name "" specified in the ""
    parameter. Reason code: "".
    DATA #1 : String, 99 bytes
    The sum of lob/xml data length and offset is bigger than the file size
    DATA #2 : signed integer, 8 bytes
    10753
    DATA #3 : signed integer, 8 bytes
    156044
    DATA #4 : signed integer, 8 bytes
    164062

  12. #12
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    What is the number of bytes in the LOB files for those first 10 rows?

    What is the file-system-type for /transfer_db2/VWSABNA/TS_MLPSN/LOB_07/ and in particular is large file support enabled for that file system?

    Also compare the file-size between the source and target system in case any file transfer was not in binary mode.

    Also ensure that accounts used for the HPU step, and the account used for the load/import has no ulimit issues.
    Last edited by db2mor; 08-26-14 at 10:58.

  13. #13
    Join Date
    Aug 2012
    Posts
    70
    here is the list of all LOBS with size (bytes)

    /transfer_db2/VWSABNA/TS_MLPSN/LOB_00/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 301020997
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_00/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 757424
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_01/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 447543990
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_01/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 310702472
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_02/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 972300
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_02/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 322281443
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_03/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 326564504
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_03/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 935450
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_04/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 1283056
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_04/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 2044817
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_05/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 1413940
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_05/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 1395471
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_06/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 88903
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_06/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 293696873
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_07/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 1303087
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_07/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 164062
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_08/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 1139209
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_08/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 1127177
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_09/TS_MLPSN__MLPSN_SNT_ATTACH.e00001.000 755044
    /transfer_db2/VWSABNA/TS_MLPSN/LOB_09/TS_MLPSN__MLPSN_SNT_ATTACH.e00002.000 397521540


    Filesystem is nfs and allows large files

  14. #14
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    So the lobfiles are also small, few hundred megabytes.
    Check file naming convention for LOBSINFILE (implied by LOBS FROM).
    Maybe the HPU version has a different file naming convention for lob file names than what db2 import /load expects ?
    Are you able to show the LLS values for the first 10 rows of the ixf ?

  15. #15
    Join Date
    Aug 2012
    Posts
    70
    I can see the firat 10 rows and the values, but I don't know how I can make sure that there are correct

Posting Permissions

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