| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-26-06, 04:40
|
|
Registered User
|
|
Join Date: Jul 2006
Location: Pune , India
Posts: 433
|
|
|
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
|
|
12-26-06, 08:16
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
|
|
12-27-06, 09:59
|
|
Registered User
|
|
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
|
|
12-27-06, 10:09
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
|
|
12-28-06, 02:39
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
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 02:46.
|
12-28-06, 03:51
|
|
Registered User
|
|
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
|
|
12-28-06, 05:24
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
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 05:32.
|
12-28-06, 07:07
|
|
Registered User
|
|
Join Date: Jul 2006
Location: Pune , India
Posts: 433
|
|
hmm...
time for me to learn some very basic DOS scripting :-)
|
|
12-28-06, 09:25
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
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
|
|
12-29-06, 01:46
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Import Command
