import a asc file to db2 table

Can any db2 gurus give me the syntax to load a asc file generated from oracle table having 1 column defined as CLOB, there are no delimiters between each row, each row is a query statement ..

Table from oracle - TEST_QUERY - column 1 - QUERY defined as CLOB.

it has around 8 rows to start with and each row is a query statement..

I want to import to table TEST_QUERY in db2 by using a flat file

let me know if you need more info..
mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
how big is the clob ?
do you have the corrsponding table defined in db2?
you should use the import command, it has a cluase - lobs from where you specify where the file that contains the lobs is
check it out here
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/core/r0008304.htm
0
mahjagAuthor Commented:
Dear Momi Sabag

Thanks for your response,file is not big it has 8453 bytes of 8 rows and each row is a insert statment with a select clause. let me know if you need more info.

We should not treat as CLOB since its source data is from oracle CLOB table, I had already converted the table to asc flat file and I need steps to import to db2 table, I defined the same table as varchar(32000). let me know if you can provide steps..
0
mahjagAuthor Commented:
Any updates.. I desparatly need the script to import asc file, any examples to start will be a great help!!
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mahjagAuthor Commented:
My flat file looks like this..
INSERT INTO TEST_TABLE (id_value,st_type,st_uom,b_flag,st)
                    SELECT col1,col2,col3 from TEST_QUERY....about 15 lines of query and then the next insert statement..
INSERT INTO TEST_TABLE (id_value,st_type,st_uom,b_flag,st)
 
if I use 
db2 import from test_table.txt of del insert into test_table
I am getting 144 rows since it took each line as a row to insert, I want to do inserts based on insert statement..

Open in new window

0
mahjagAuthor Commented:
Hi db2 gurus
I can add a new line between my insert commands and also add "@" symbol at the end and the flat file looks like
INSERT INTO TEST_TABLE (id_value,st_type,st_uom,b_flag,st)
                    SELECT col1,col2,col3 from TEST_QUERY....about 15 lines of query and then the next insert statement..
@
INSERT INTO TEST_TABLE (id_value,st_type,st_uom,b_flag,st)
 SELECT col1,col2,col3 from TEST_QUERY....about 15 lines of query and then the next insert statement..
@
and so on

Can i have the script to load this to a table.. Please............
0
mahjagAuthor Commented:
I only know db2 has coldel but that is column delimiter and not row delimiter.. I dont know where to find that ,, any help..........................
0
mahjagAuthor Commented:
I increased the points as 50 seems not reasonable.
0
momi_sabagCommented:
what do you intend to do with the data once it's in the table ?
db2 won't load couple of rows from the file into a single row, but maybe we can load it like that and manipulate it using sql later,
what do you need the data for ?
0
mahjagAuthor Commented:
Dear Momi

After I get the data in the table I am using the Stored proceudre which I posted in my another question - you are responding to that as well - I feel I am lucky- since you are handiling both my questions -

I am using stored procedure to execute the query..I can attach the same code here for SP, I dont know how to reference my other question here..
0
mahjagAuthor Commented:
any updates as to how to load the data.. I am kind of dependent on this answer to proceed to my next question and stalled.
0
momi_sabagCommented:
how about trying to add another column to the table with the statement
that column will be an identity column , this way in your stored procedure that runs the statements
you can code logic that will fetch the statement from more than one row and assemble it
it will be much easier than figuring out how to load the data in a way that will merge couple of rows into one
0
mahjagAuthor Commented:
I could not visualize your solution, what in terms of idenitify column will alleviate this problem, not sure, can you describe more in terms of structure..
0
momi_sabagCommented:
add an identity column to the table
this column will automatically generate increasing values
after you load the data from the file to the table in your code that select the data you add -
order by identity_column_name
this will assure that the sql statement return in the same order it was in the file
then in your loop instead of doing
fetch
execute immediate

you do something like

sql_stmt = ''
fetch into :hv
if substr(:hv,1,7)<>'INSERT ' then
  sql_stmt = sql_stmt || hv
else
  execute sql_stmt
  sql_stmt = hv
end if

fetch
0
mahjagAuthor Commented:
so if I understand that correctly test_table above that inserts the record will have another row called identity column and its value will be a sequence?  how do I load the flat file into a table - I think I am missing a step..
0
momi_sabagCommented:
the identity is a column not a row
you add an identity column to your table
when you do so, by default, db2 will generate ever increasing values for that column, so you don't need to provide a value when your insert records or import records from a flat file
0
mahjagAuthor Commented:
my problem is not adding identity column but to find a way to import that file to db2, can you provide me scripts to import the data..
0
momi_sabagCommented:
you just import the script the same way you did before
db2 import from test_table.txt of del insert into test_table

and the procedure code will handle the concatenation
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.