INSERT MORE THAN ONE ROWS IN SPUFI

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
technokrat
Member
Posts: 6
Joined: Wed Jun 17, 2009 11:56 am
Location: Bangalore

INSERT MORE THAN ONE ROWS IN SPUFI

Post by technokrat » Wed Jun 17, 2009 2:04 pm

HI,
can anyone help me out in inserting two rows at a time
i tried with the following syntax but it gave me error.

INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
VALUES('RAJU',8888,'BANGALORE'),
('JOHN',7777,'CHENNAI'),
('KEVIN',7676,'NEW YORK');


:?:

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Wed Jun 17, 2009 2:21 pm

Hi,

Syntax looks correct.
what error you are receiving?
Natarajan
Chennai

technokrat
Member
Posts: 6
Joined: Wed Jun 17, 2009 11:56 am
Location: Bangalore

Post by technokrat » Wed Jun 17, 2009 2:27 pm

SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",".

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Wed Jun 17, 2009 2:49 pm

what is your db2 version.
Natarajan
Chennai

technokrat
Member
Posts: 6
Joined: Wed Jun 17, 2009 11:56 am
Location: Bangalore

Post by technokrat » Wed Jun 17, 2009 2:56 pm

its a 8.1 version

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Jun 17, 2009 3:00 pm

you have to use UNION for multiple inserts when you are providing values.
because you can not have multiple values for an insert, in all db2 versions.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

DB2 MULTI-ROW INSERT COBOL - DB2

Post by Natarajan » Wed Jun 17, 2009 3:08 pm

with DB2 version 8 multi-row insert will work.
Not sure whether it will work with spufi or not.

This DB2 feature work with any application program like COBOL.
This syntax may work on DB2 LUW for windows, unix and Linux.
Last edited by Natarajan on Wed Jun 17, 2009 3:10 pm, edited 1 time in total.
Natarajan
Chennai

technokrat
Member
Posts: 6
Joined: Wed Jun 17, 2009 11:56 am
Location: Bangalore

Post by technokrat » Wed Jun 17, 2009 3:09 pm

Can u plz modify the above query for using UNION ??? :?:

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Jun 17, 2009 3:17 pm

Can u plz modify the above query for using UNION ???
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
VALUES('RAJU',8888,'BANGALORE')
UNION
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
('JOHN',7777,'CHENNAI')
UNION
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
('KEVIN',7676,'NEW YORK')
UNION
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
('JOHN',7777,'CHENNAI')
UNION
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
('KEVIN',7676,'NEW YORK');
with DB2 version 8 multi-row insert will work.
could you please provide an example?
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Wed Jun 17, 2009 3:23 pm

Hi Dick,

My statement has come from following ibm material on DB2.
http://publib.boulder.ibm.com/infocente ... g/fmlt.htm
Natarajan
Chennai

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

DB2 MULTI-ROW INSERT

Post by Natarajan » Wed Jun 17, 2009 3:26 pm

Technokrat,

You can also use following sytax for multi-row insert from spufi.
It is very similar to what dick has suggested.

Code: Select all

INSERT INTO EMPLOYEE

SELECT  'RAJU',8888,'BANGALORE' from SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'JOHN',7777,'CHENNAI' from SYSIBM.SYSDUMMY1
UNION ALL
SELECT  'KEVIN',7676,'NEW YORK' FROM SYSIBM.SYSDUMM1;
Natarajan
Chennai

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Jun 17, 2009 4:27 pm

Natarajan,

you are correct. I did not bother to read the manual until after I posted (and before your subsequent posts).

sorry, I will research before posting.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

RaviTejaG
Member
Posts: 15
Joined: Tue Apr 14, 2009 3:49 pm
Location: MYSORE

Post by RaviTejaG » Wed Jul 29, 2009 5:51 pm

Hi Natarajan,
Can you explain above query

SELECT 'RAJU',8888,'BANGALORE' from SYSIBM.SYSDUMMY1

why u are selecting the record from SYSIBM.SYSDUMMY1

and what will happen here , when we doing union all

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Jul 29, 2009 9:25 pm

RaviTejaG,

you need to read a little bit. SYSIBM.SYSDUMMY1 is a table (provided by db2-ibm) that allows you to select something that does not exist in a table.

you can set :timestamp = current_timestamp
or you can
select current_timestamp into :timestamp from sysibm.sysdummy1
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others