Page 1 of 1
Loading from db into a squential file without deilimeter.
Posted: Thu Nov 09, 2006 1:23 pm
by laxmi_etl
Hi-
I need to load data from database to sequential file without a deilimter, but with a fixed width for each coulum.
Like Column A takes first 1- 7 digits and Column B 8-16 , and column C is 16-30 etc..
Any thoughts , pls help.
Thanks
Posted: Thu Nov 09, 2006 1:51 pm
by samsuf2002
set the delimiter in seq file as 'none'
Posted: Thu Nov 09, 2006 1:52 pm
by DSguru2B
and the sqltype as char.
Posted: Thu Nov 09, 2006 2:26 pm
by laxmi_etl
any more thoughts??
Posted: Thu Nov 09, 2006 3:23 pm
by talk2shaanc
laxmi_etl wrote:any more thoughts??
your design should be
db2 ===> seq file
DB2 should have a normal SQL.
and your Sequential file should have below properties.
Record delimeter = UNIX newline
Record Length =fixed
Delimeter = none
Posted: Fri Nov 10, 2006 12:05 am
by tagnihotri
"and the sqltype as char" While doing this check for the paded character (i.e. default env variable) because you may end up padding your char fields with funny characters which may not be even printable.
Re: Loading from db into a squential file without deilimeter
Posted: Fri Nov 10, 2006 11:40 am
by csrazdan
I can give you one more idea. You can use SQL statement to format data. This was the way we used to unload data from a DB table into fixed format files in good old days:
Consider a table emp:
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER(5)
FNAME VARCHAR2(15)
MNAME VARCHAR2(1)
LNAME VARCHAR2(15)
ZIP NUMBER(5)
Following sql will format the data into fixed width record.
SQL> select lpad(empid,5)||rpad(fname,15)||mname||rpad(lname,15)||lpad(zip,5) EMP_REC from emp;
If you want you can embed this SQL in datastage job to create a file.
How about that... hope it helps.