Import from non-delimited ASCII file
0) We have DB2 7.2 EE server on win NT 4 & 2000 OS.
1) We have a table having 10 not null columns. There are no primary keys, foreign keys, check constraints & default values.
2) We have a non-delimited ASCII text file having 4 rows and 10 column values. This we were successful in importing to our table.
3) Wehave another non-delimited ASCII text file where there are 4 rows but values for 5th and 10th column are missing. Can we import this data into our table? We did not succeed.
4) If yes, then how? Please give detailed steps of import.
5) Is it true that load utility is same as import utility. The difference is we use load for loading huge data into tables.
1) We have a table having 10 not null columns. There are no primary keys, foreign keys, check constraints & default values.
2) We have a non-delimited ASCII text file having 4 rows and 10 column values. This we were successful in importing to our table.
3) Wehave another non-delimited ASCII text file where there are 4 rows but values for 5th and 10th column are missing. Can we import this data into our table? We did not succeed.
4) If yes, then how? Please give detailed steps of import.
5) Is it true that load utility is same as import utility. The difference is we use load for loading huge data into tables.
Oops, forget my comment -- I didn't notice the
'not null'
'not null'
If you do not have any default values, you won't
be able to load/import that data where some of
the columns are missing.
be able to load/import that data where some of
the columns are missing.
ASKER
mglxxx, I will be working further on this tommorow & if no further doubts, I will accept the given comment as answer.
Please go through my question titled 'DB2 7.2/8 EE vs EEE'
Please go through my question titled 'DB2 7.2/8 EE vs EEE'
ASKER
mglxxx, I am sorry. In the first place, if all columns are not null, then there should be some values. Eveb if they are char(1), i suppose ' ' can be inserted and not '' which is treated as a null.
When we export data from table containing columns having ' ', then will there be any data under that column in the non-delimited/delimited ASCII text like file generated by export from source database. If no, then how do we do the import.
Are there any other methods apart from export & import/load. In third party tools or MS SQL Server or transferring to excel worksheet/MS Access before importing to target DB2 database. You did not keep a comment for my point number 5).
When we export data from table containing columns having ' ', then will there be any data under that column in the non-delimited/delimited ASCII text like file generated by export from source database. If no, then how do we do the import.
Are there any other methods apart from export & import/load. In third party tools or MS SQL Server or transferring to excel worksheet/MS Access before importing to target DB2 database. You did not keep a comment for my point number 5).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mglxxx, thank you. i am giving it to you. Can you mail me the perl script to convert ASC into DEL with appropriate instructions. My mail id is k_murli_krishna@hotmail.co
ASKER
We did manage to create a formetted single space in text pad and this file whether delimited or non-delimited we successfully imported into our table.
For a quick hack, I'd need the format of the
data file and the data types of the columns to
be loaded from that file. A script which is specific
to that problem is quickly done, a generic script
will take considerably more time and effort.
data file and the data types of the columns to
be loaded from that file. A script which is specific
to that problem is quickly done, a generic script
will take considerably more time and effort.
behaviour of load and import is identical.
The only difference is that for load you can switch
off some of the syntax checking during parsing by
using the 'fastparse' option.
Here's an example:
create table junk ( a varchar(3),
b varchar(3),
c varchar(3));
A data file containing only data for column 'a' and
'c' (let's assume the file is called junk.dat):
A1AC1C
A2AC2C
A3AC3C
The import command to fill only colums 'a' and 'c' from
junk.dat:
import from junk.dat of asc
method L (1 3, 4 6)
replace into junk (a, c);