Insert multiple rows from SPUFI

jamshirqureshi · New User Joined: 13 Aug 2006 Posts: 20 Location: mumbai

Hello Friends,

Has anyone tried to insert multiple SQL rows from SPUFI into DB2.

If its insert please give me the syntax.

thx
Jamz

somnath · New User Joined: 21 Mar 2005 Posts: 18 Location: India

I don't think that you can insert multiple rows using spufi. You can do so by using Cobol-DB2 program or you can use multiple insert statement in spufi.

guptae · Posted: Tue Jun 26, 2007 12:36 pm

Hi jamz,

What do u mean by insert multiple rows from SPUFI?
Do u want to know whether we can use Insert ...select?

jamshirqureshi · New User Joined: 13 Aug 2006 Posts: 20 Location: mumbai

I mean i want to insert Mulptile rows using single ISNERT statement through SPUFI
Is it possible in SPUFI , i know we can do this using cursor.

And i dont want multiple insert statment also. i want it in single statment

example

Insert into Student_table
(stud_name, Roll_no, Mark)
values ('Ram', 101, 255),
('Shyam' 102, 350)

so like this but if i run this query in spufi it ll show the -104 error which illegl ',' symbol.

which is values ('Ram', 101, 255), <=== here

so what i wanted is , is there any other way to continue to insert mupltiple rows using single INSERT statment through SPUFI

Pandora-Box · Posted: Tue Jun 26, 2007 2:54 pm

Intead of this why dont you try to make use of LOAD utility.It will help you insert more number of rows.

jamshirqureshi · New User Joined: 13 Aug 2006 Posts: 20 Location: mumbai

Hi,

I know LOAD utility, but i wanted to know that we can do like this ?

jamshirqureshi · New User Joined: 13 Aug 2006 Posts: 20 Location: mumbai

But for LOAD utility we need Multiple INSERT statement

Pandora-Box · Posted: Wed Jun 27, 2007 11:20 am

I guess you have got the wrong conception about LOAD utility please go through the manuals it is very clear.............

Phrzby Phil · Posted: Wed Jun 27, 2007 7:08 pm

I do not think you can use one INSERT for multiple rows. I tried this, and then began using LOAD. LOAD is very nice, well documented, and easy to use.

I recommend you test first with very small files to achieve a comfort level. I use a personal sandbox table with different kinds of columns, nullable and not, just for testing.

Note that UNLOAD creates LOAD control cards that correspond to the UNLOADed data, so you can UNLOAD first, then format your data-to-be-loaded similarly and use the generated LOAD controls.

Caution: I believe that the LOAD replace optiion works by tablespace, not table, so if you have tables sharing a tablespace, watch out!

Also watch for location/value of null indicators.

prasadvrk · Posted: Wed Jun 27, 2007 7:26 pm

Jamshir,

If you are desparate about using SPUFI alone, you can have as many INSERT statements as you want in the same SPUFI file separated by ;

eg.
Insert into Student_table
(stud_name, Roll_no, Mark)
values ('Ram', 101, 255);

Insert into Student_table
(stud_name, Roll_no, Mark)
('Shyam' 102, 350) ;

and run them all at once, SPUFI can run multiple queries. I hope this answers your problem without using the LOAD but always LOAD is advisable.

vini_srcna · Posted: Thu Jun 28, 2007 9:57 am

Hi Guys,

DB2 Manuals says we can insert multiple rows in a single insert statement. As a matter of fact this syntax works only on DB2 LUW (Linux, Unix, Windows). It is not supported in DB2 V 7.1 for Z/os atleast. I didnt get a chance to check it on V8 and Viper yet. If am right it is not supported in V8 too.

wanderer · Posted: Fri Jun 29, 2007 5:10 am

V8 in z/OS supports multiple row inserts but only through an application program where you can use the name of an array and all are inserted in one go. I don't think it is possible using spufi.

vini_srcna · Posted: Fri Jun 29, 2007 12:36 pm

I shall agree. V8 Came with Multi row fetch, Insert & Update. It would happen through application program and only in V8 after.

girias · New User Joined: 09 Feb 2007 Posts: 26 Location: Chennai

Hello All, Whether DB2 V9 supports this?

dick scherrer · Posted: Tue Mar 31, 2009 10:01 pm

Hello,

sushanth bobby · Posted: Thu Apr 02, 2009 8:37 pm

WOW GUYZ,

2 YEAR old discussion brought back to live. But still no answer ?
I SEAROOGLED & came across an interesting articleon this but on a different Database.

I checked something like this, it worked in SPUFI. Iam on V8.

girias · New User Joined: 09 Feb 2007 Posts: 26 Location: Chennai

Hi Sushanth,

WoW!!! it worked. gr8!!... Thanks a lot....

sushanth bobby · Posted: Fri Apr 03, 2009 1:45 am

Happy to help! & Happy to learn!

Let this post R.I.P,
Sushanth Bobby