Results 1 to 10 of 10

Thread: Import Command

  1. #1
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433

    Unanswered: Import Command

    I am having a file having four rows as
    2342
    234234
    345
    234253
    Now I want to import them in a table so that all four lines goes into 4 different columns(A,B,C,D) in a single row in a table.
    Is there any option in Import Command ?

    Regards
    Rahul Singh

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    A lot depends on how the data in the file is formatted. If they are separated by commas, you can use the IMPORT command using type DEL.

    Andy

  3. #3
    Join Date
    Jun 2006
    Posts
    471

    import

    as indicated, the file is 4 rows and we want 4 columns in 1 row
    this is not possible with import (I think)
    a small program (perl/rexx....) should do
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Sorry, I misread the original post. You are right, IMPORT will not be able to handle this file. You will need to write something to do it. I suggest you use a language you are most familiar with.

    Andy

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by rahul_s80
    I am having a file having four rows as
    2342
    234234
    345
    234253
    And what is the next row in table? If I understand correctly the next four rows in file is one new row in table? Is it?

    You can insert into final table with import and SQL support. You don't need to write a program.

    Try this out:
    1. input file c:\input.txt looks like:
    Code:
    100
    200
    300
    400
    1000
    2000
    3000
    4000
    2. create temporally table (you can delete it when finished):
    Code:
    CREATE TABLE DB2ADMIN.TEMP_TABLE (DATA INT)
    3. import input.txt file into temporally table
    Code:
    IMPORT FROM C:\INPUT.TXT OF DEL INSERT INTO DB2ADMIN.TEMP_TABLE
    4. create final table (if not already created)
    Code:
    CREATE TABLE DB2ADMIN.FINAL_TABLE (A INT, B INT, C INT, D INT)
    5. insert first 4 rows from temp_table into one row of final_table.
    Code:
    INSERT INTO DB2ADMIN.FINAL_TABLE 
    SELECT SUM(A), SUM(B), SUM(C), SUM(D) FROM (
    SELECT DATA AS A, 0 AS B, 0 AS C, 0 AS D FROM (SELECT ROW_NUMBER() OVER() AS NUM, DATA FROM DB2ADMIN.TEMP_TABLE) AS TEMP1 WHERE NUM=1
    UNION ALL
    SELECT 0 AS A, DATA AS B, 0 AS C, 0 AS D FROM (SELECT ROW_NUMBER() OVER() AS NUM, DATA FROM DB2ADMIN.TEMP_TABLE) AS TEMP1 WHERE NUM=2
    UNION ALL
    SELECT 0 AS A, 0 AS B, DATA AS C, 0 AS D FROM (SELECT ROW_NUMBER() OVER() AS NUM, DATA FROM DB2ADMIN.TEMP_TABLE) AS TEMP1 WHERE NUM=3
    UNION ALL
    SELECT 0 AS A, 0 AS B, 0 AS C, DATA AS D FROM (SELECT ROW_NUMBER() OVER() AS NUM, DATA FROM DB2ADMIN.TEMP_TABLE) AS TEMP1 WHERE NUM=4
    ) AS TEMP2
    6. delete inserted rows from temp_table
    Code:
    DELETE FROM DB2ADMIN.TEMP_TABLE WHERE DATA IN (SELECT DATA FROM (SELECT ROW_NUMBER() OVER() AS NUM, DATA FROM DB2ADMIN.TEMP_TABLE WHERE) TEMP3 WHERE NUM IN (1,2,3,4))
    7. repeat steps 5 and 6 until all rows are inserted into final_table. I advice to create a txt file (e.g. command.txt) and copy/paste 5 and 6 command multiple times (this should be an easy task) then execute txt file multiple times by:
    Code:
    db2 -tf command.txt
    until you get an error of "No row was found for FETCH, UPDATE or DELETE" by delete command.

    8. you will probably need to delete and empty row (rows) from final_table
    Code:
    DELETE FROM DB2ADMIN.FINAL_TABLE WHERE A IS NULL
    Hope this helps,
    Grofaty
    Last edited by grofaty; 12-28-06 at 03:46.

  6. #6
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    hi grofty
    Thanks for your advice and efforts
    Actually what i wanted to achieve was to insert records into a table (the details of the free space on various drives in the server so that Backup may be scheduled after that )
    In My case the file will be having only four rows at max.
    and my script is more or less same as that of urs

    DDL :
    create table Eone.disk_temp (date date default current date, severname varchar(30), Diskno varchar(50), space varchar(100));
    create table eone.disk_dtl (severname varchar(30), C varchar(100) , D varchar(100), E varchar(100), F varchar(100));

    Task Center Script:
    @ Echo off
    C:
    dir | findstr -i free > C:\Task.Center.Folder\Disk\dir.txt
    d:
    dir | findstr -i free >> C:\Task.Center.Folder\Disk\dir.txt
    F:
    dir | findstr -i free >> C:\Task.Center.Folder\Disk\dir.txt

    db2 connect to sample
    db2 import from C:\Task.Center.Folder\Disk\dir.txt of del modified by coldel$ method P (1) insert into eone.disk_temp (space)
    db2 update eone.disk_temp set diskno= char(0+row_number() over() ) , servername='TIGER'
    db2 insert into eone.disk_dtl (servername,c,d,E) (SELECT * from TABLE (VALUES('TIGER', (select space from Eone.disk_Temp where Diskno='1' ),(select space from Eone.disk_Temp where Diskno='2' ),(select space from Eone.disk_Temp where Diskno='3' ))) AS Q(servername,C,D,E) )
    db2 update eone.disk_dtl set C=char(cast (replace(substr(replace(C,'bytes free',''),10),',','') as decimal(12,2) )/1024/1024/1024 ) where date=current date and servername='TIGER'
    db2 update eone.disk_dtl set D=char(cast (replace(substr(replace(D,'bytes free',''),10),',','') as decimal(12,2) )/1024/1024/1024 ) where date=current date and servername='TIGER'
    db2 update eone.disk_dtl set E=char(cast (replace(substr(replace(E,'bytes free',''),10),',','') as decimal(12,2) )/1024/1024/1024 ) where date=current date and servername='TIGER'
    db2 delete from eone.disk_temp
    db2 connect reset

    its just a rough sketch , and i will be modifing that soon
    once again, Thanks

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    oooohh this is completely different story. One was is to follow my idea above but without 7 and 8 step.

    But if you prefer solving the problem by batch file script there can be more simple script. Try this out (you need to make it work for your sample):

    Code:
    rem Get free disk info from disk C
    c:
    for /f "tokens=3" %%i in ('dir /-C ^| findstr -i free') do (
         set disk_c_free=%%i
           	)
    
    rem Get free disk info from disk D
    d:
    for /f "tokens=3" %%i in ('dir /-C ^| findstr -i free') do (
         set disk_d_free=%%i
           	)
    
    rem Get free disk info from disk E
    rem Add the code as appropriate...
    
    rem If you like to display the variable value you can use echo command.
    echo %disk_c_free%
    echo %disk_d_free%
    
    rem Then just use insert statement into final table
    db2 connect to sample
    db2 insert into tab values ('TIGER', %disk_c_free%, %disk_d_free%)
    db2 connect reset
    NOTES:
    Command "dir /-C" gets only the numbers without separators.
    Character "^" must be used in batch files if "|" sign is used.

    This solution is without import statement, without temporally table and without file C:\Task.Center.Folder\Disk\dir.txt.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 12-28-06 at 06:32.

  8. #8
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    hmm...
    time for me to learn some very basic DOS scripting :-)

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    you can also use:
    Code:
    for /f "tokens=3" %%i in ('dir c: /-C ^| findstr -i free') do (
         set disk_c_free=%%i
           	)
    instread of
    Code:
    c:
    for /f "tokens=3" %%i in ('dir /-C ^| findstr -i free') do (
         set disk_c_free=%%i
           	)
    So one command less...

    Hope this helps,
    Grofaty

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by rahul_s80
    time for me to learn some very basic DOS scripting :-)
    Hi,
    I recommend:
    - MS-DOS help and commands
    - The power of CMD.EXE commands

    Hope this helps,
    Grofaty

Posting Permissions

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