Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Syntax to insert multiple rows from SPUFI

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Syntax to insert multiple rows from SPUFI
Reply with quote

Hello Frnds,

Has anyone tried to insert multiple rows from SPUFI.

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    Post subject: Re: Multiple Rows in SPUFI
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: 1187
Location: Bangalore,India

PostPosted: Tue Jun 26, 2007 12:36 pm    Post subject:
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    Post subject:
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

Moderator


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

PostPosted: Tue Jun 26, 2007 2:54 pm    Post subject:
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    Post subject:
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    Post subject:
Reply with quote

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

Moderator


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

PostPosted: Wed Jun 27, 2007 11:20 am    Post subject:
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

Active Member


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

PostPosted: Wed Jun 27, 2007 7:08 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Re: Syntax to insert multiple rows from SPUFI
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    Post subject:
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    Post subject: Reply to: Syntax to insert multiple rows from SPUFI
Reply with quote

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

Site Director


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

PostPosted: Tue Mar 31, 2009 10:01 pm    Post subject:
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: 1013
Location: India

PostPosted: Thu Apr 02, 2009 8:37 pm    Post subject:
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    Post subject: Reply to: Syntax to insert multiple rows from SPUFI
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: 1013
Location: India

PostPosted: Fri Apr 03, 2009 1:45 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts How to insert a lengthy string havin... vidyaa DB2 7 Thu Aug 25, 2016 5:20 pm
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us