Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    6

    Unanswered: Help me with import!

    Hi,
    I am trying to migrate a database from Sybase to db2. My DB2 version is 'v8.1.11.973' FixPack 11. I have unloaded the data into files and am trying to import it in my db2 database in a C++ program. The code looks like this:

    sSQL.Format("import from 'f:/unload/%d.dat' of del modified by coldel^ commitcount 5000 messages 'f:/unload.txt' insert into SMART_GROUP.%s;",nTableID,(LPCSTR)tableName);
    {
    DBExt.GetCon().ExecuteSQL(sSQL);
    }

    That is of course in a loop. I am receiving the following errror - sql0007n - 'The character "^" following " del modified by coldel" is not valid'.I don't get this error when I execute the same command in the Command Editor. When I change the column delimiter to ',' I get the error - SQL0104N 'An unexpected token "import" was found following "BEGIN-OF-STATEMENT".' . I just read somewhere that it is impossible to import data through ODBC, I guess that is the problem. So my question is how can I do a mass storage import in DB2?

    Thanks in advance,Emil
    Last edited by zsevgymko; 05-23-06 at 03:56.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Why do you need to do the import command from a C++ program? It is easier to do it from a shell script on a machine with a DB2 client installed (or directly on the database server).
    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
    May 2006
    Posts
    6
    Well I don't really need it to be executed from a C++ program,it was simply the way I did it previously when I was migrating another database from Sybase to Postgre, I had no problems then. Can you give me any hints/links to what that shell script must look like?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by zsevgymko
    Well I don't really need it to be executed from a C++ program,it was simply the way I did it previously when I was migrating another database from Sybase to Postgre, I had no problems then. Can you give me any hints/links to what that shell script must look like?
    You basically have the command in your code:

    db2 "import from 'f:/unload/file.dat' of del modified by coldel^ commitcount 5000 messages 'f:/unload.txt' insert into schema.table-name"

    You can use the Control Center to generate most commands (including IMPORT) and see the script without submitting it.
    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
    May 2006
    Posts
    6
    Quote Originally Posted by Marcus_A
    You basically have the command in your code:

    db2 "import from 'f:/unload/file.dat' of del modified by coldel^ commitcount 5000 messages 'f:/unload.txt' insert into schema.table-name"

    You can use the Control Center to generate most commands (including IMPORT) and see the script without submitting it.
    I know all of that, but I want to generate a script which will transfer the data of ALL my tables at once. I don't want to execute every single import by hand! ~1000 tables * ~50 servers = Nightmare! The code snippet which I have posted is executed in a loop for ALL the tables in the database, you can see the tableName parameter. Any ideas?
    Last edited by zsevgymko; 05-23-06 at 06:06.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What you can do is generate the required scripts based on the tables in syscat.tables something like
    Code:
    select 'db2 "import from f:/unload/' || SYSCAT.TABLES.TABNAME || '.dat of del ' ||
            'modified by coldel^ commitcount 5000 messages f:/unload.txt ' || 
            'insert into schema.' || SYSCAT.TABLES.TABNAME || ' " '
    from SYSCAT.TABLES 
    where syscat.schemaname = 'MY_SCHEMA'
    select the gerenated tekst and execute it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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