IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Insert multiple rows from SPUFI


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
jamshirqureshi

New User


Joined: 13 Aug 2006
Posts: 20
Location: mumbai

PostPosted: Tue Jun 26, 2007 10:27 am
Reply with quote

Hello Friends,

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

If its insert please give me the syntax.

thx
Jamz
Back to top
View user's profile Send private message
somnath

New User


Joined: 21 Mar 2005
Posts: 18
Location: India

PostPosted: Tue Jun 26, 2007 10:58 am
Reply with quote

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.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Tue Jun 26, 2007 12:36 pm
Reply with quote

Hi jamz,

What do u mean by insert multiple rows from SPUFI?
Do u want to know whether we can use Insert ...select?
Back to top
View user's profile Send private message
jamshirqureshi

New User


Joined: 13 Aug 2006
Posts: 20
Location: mumbai

PostPosted: Tue Jun 26, 2007 1:20 pm
Reply with quote

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
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Jun 26, 2007 2:54 pm
Reply with quote

Intead of this why dont you try to make use of LOAD utility.It will help you insert more number of rows.
Back to top
View user's profile Send private message
jamshirqureshi

New User


Joined: 13 Aug 2006
Posts: 20
Location: mumbai

PostPosted: Tue Jun 26, 2007 3:36 pm
Reply with quote

Hi,

I know LOAD utility, but i wanted to know that we can do like this ?
Back to top
View user's profile Send private message
jamshirqureshi

New User


Joined: 13 Aug 2006
Posts: 20
Location: mumbai

PostPosted: Tue Jun 26, 2007 3:40 pm
Reply with quote

But for LOAD utility we need Multiple INSERT statement
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Jun 27, 2007 11:20 am
Reply with quote

I guess you have got the wrong conception about LOAD utility please go through the manuals it is very clear.............
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1049
Location: Richmond, Virginia

PostPosted: Wed Jun 27, 2007 7:08 pm
Reply with quote

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.
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Wed Jun 27, 2007 7:26 pm
Reply with quote

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.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Jun 28, 2007 9:57 am
Reply with quote

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.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Jun 29, 2007 5:10 am
Reply with quote

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.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Jun 29, 2007 12:36 pm
Reply with quote

I shall agree. V8 Came with Multi row fetch, Insert & Update. It would happen through application program and only in V8 after.
Back to top
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Tue Mar 31, 2009 4:12 pm
Reply with quote

Hello All, Whether DB2 V9 supports this?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Mar 31, 2009 10:01 pm
Reply with quote

Hello,

Quote:
Whether DB2 V9 supports this
Supports what. . .?

You have replied to a topic that has been inactive for almost 2 years.

Suggest you start a new topic for your question and make sure that your question is completely/clearly presented.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Apr 02, 2009 8:37 pm
Reply with quote

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.
Code:
insert into bobt1.country                                     
select 301,'INDIA','PARRYS Station' from sysibm.sysdummy1     
union all                                                     
select 302,'INDIA','AMAMJIkarai' from sysibm.sysdummy1;       


Answers will give you pleasant sleep,
Sushanth Bobby
Back to top
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Fri Apr 03, 2009 12:08 am
Reply with quote

Hi Sushanth,

WoW!!! it worked. gr8!!... Thanks a lot....
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Apr 03, 2009 1:45 am
Reply with quote

Happy to help! & Happy to learn!

Let this post R.I.P,
Sushanth Bobby
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts Reflection 3270 screen shows only 23 ... Compuware & Other Tools 6
No new posts SORT JCL to merge multiple tow into s... DFSORT/ICETOOL 6
No new posts Using Multiple IFTHEN and WHEN condit... SYNCSORT 12
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
Search our Forums:

Back to Top