Hi Can anybody tell me what we need to do when we are exporting 1)integer 2)character 3)date to IBM environment(as client).. I am getting junk characters... Can anyone tell what kind of rules we need to follow when client is IBM and windows...while fast exporting the file
1)integer: Int_Col (FORMAT '99....') (CHAR(n)) - here n is the maximum value you are expecting in the int field if you are expecting maximum value as 99, then it will be Int_Col (FORMAT '99') (CHAR(2)) 2)character: Char_Col (CHAR(n)) - here n is the length specified while creating the table 3)date: Date_Col (FORMAT 'YYYY-MM-DD') (CHAR(10)) or Date_Col (FORMAT 'YYYYMMDD') (CHAR(8))
To export into a fixed width flat file run this query: SELECT ColumnID, ColumnLength, ColumnType, ColumnName, CASE WHEN ColumnType = 'CF' THEN ColumnLength -- char WHEN ColumnType = 'CV' THEN ColumnLength / 2 --varchar WHEN ColumnType = 'I' THEN 11 --integer WHEN ColumnType = 'I1' THEN 4 --byte integer WHEN ColumnType = 'I2' THEN 6 --small integer WHEN ColumnType = 'D' THEN 19 --decimal WHEN ColumnType = 'F' THEN 19 --float WHEN ColumnType = 'DA' THEN 10 --date WHEN ColumnType = 'TS' THEN 19 -- timestamp ELSE NULL END AS RealColumnLength FROM dbc.COLUMNS WHERE dbc.COLUMNS.tableName = 'YOUR_TABLE' ORDER BY ColumnID
and then for the fastexport script concatenate "cast (ColumnName as char(RealColumnLength))||" for each column. Cast the resulting string to a sum(RealColumnLength). I am using a simple Excel worksheet for this.