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
 

 

DB2 SQLCODE -4700

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pjnithin

Active User


Joined: 22 Dec 2005
Posts: 116

PostPosted: Mon May 25, 2009 4:56 pm    Post subject: DB2 SQLCODE -4700
Reply with quote

I have an insert query like this:

INSERT INTO tbl1
(EMAILID,
EMPNO)
VALUES ( 'ID1',
(SELECT SUBSTR(MAX(EMPNO),1,1) ||
SUBSTR(DIGITS(CAST(SUBSTR(MAX(EMPNO),2,11)
AS INTEGER) + 1),3,8)
FROM tbl1
WHERE EMPNO LIKE 'C%'))

Basically I need to get the empno dynamically created while doing the insert. But this query fails with an sqlcode -4700. Please let me know whether there is any other method of doing this.
Back to top
View user's profile Send private message

arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Mon May 25, 2009 7:59 pm    Post subject:
Reply with quote

This might help you...

Quote:

-4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE

Explanation: Functions that this release of DB2 introduces cannot be used before new function mode has been enabled. An attempt was made to execute one of these functions. In addition, support for extending the length of a VARCHAR (supported in V7) is restricted in V8 until new function mode has been enabled. To use the new functions that have been introduced in this release of DB2, new-function mode must first be enabled. This error code will be given after an attempt to use a new function before new-function mode has been enabled. Additionally, the following statements are restricted in DB2 for z/OS Version 8 until new-function mode is enabled:
1. Altering a table to increase the length of a VARCHAR column
2. Altering a catalog table to DATA CAPTURE CHANGES

System action: The statement cannot be executed.

Programmer response: Either delay running your program until new-function mode has been enabled, or restrict your program to functions that are allowed before new-function mode is enabled.

Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon May 25, 2009 8:12 pm    Post subject:
Reply with quote

pjnithin,

I have been experimenting on vsn 9.??? and my response is SQLCODE -290, SQLSTATE=55039
Table space access is not allowed.

A process attempted to access a table space which is in an
invalid state for which the intended access is not allowed.

If the table space is in a quiesced state, ....
quiesced is not the problem here

If the table space is in any other state, only the process
which is performing the action specified is allowed access to
the table space.

I interpret the above to mean that during an INSERT,
I can not derive a value based on a subselect from the same table.

Every system,
that I have ever worked on,
derived the 'new number/code' based on a control table for new numbers/codes.

Perhaps reason there was always another table involved,
was due to the sqlstate that I received for the INSERT with the subselect.
Back to top
View user's profile Send private message
pjnithin

Active User


Joined: 22 Dec 2005
Posts: 116

PostPosted: Tue May 26, 2009 12:36 pm    Post subject: Reply to: DB2 SQLCODE -4700
Reply with quote

When I try to do an update using a subquery it does; but only insert fails. None of the explanations are very clear to me; can someone enlighten me on the real reason.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Tue May 26, 2009 4:41 pm    Post subject: Reply to: DB2 SQLCODE -4700
Reply with quote

Hi pjnithin

Your query will work in INSERT also.
What is the datatype of EMPNO? Is it CHAR or INT ? Since you are using a SUBSTR it must be a CHAR.
While specifying the arguments in the SUBSTR, you should take care for the length of the EMPNO column.

I have an insert query for a table. Here EMPNO is CHAR(6).
Code:
INSERT INTO EMPLOYEE1(EMPNO) VALUES(                           
SELECT SUBSTR(MAX(EMPNO),1,1) CONCAT                           
   SUBSTR(DIGITS(CAST(SUBSTR(MAX(EMPNO),4,3) AS INT) + 1),6,5) 
       FROM EMPLOYEE1);                                         

It is working fine.
Try it out.

Regards
Raghu[/quote]
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue May 26, 2009 4:58 pm    Post subject:
Reply with quote

Raghu,

does your query work when you have the where clause
WHERE EMPNO LIKE 'C%
in the subselect?
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Tue May 26, 2009 5:44 pm    Post subject: Reply to: DB2 SQLCODE -4700
Reply with quote

Yes dbzTHEdinosauer

In my case EMPNO starts with '0' (zero).
I used the following query

Code:
---------+---------+---------+---------+---------+---------+-----
 INSERT INTO EMPLOYEE1(EMPNO) VALUES(                           
 SELECT SUBSTR(MAX(EMPNO),1,1) CONCAT                           
    SUBSTR(DIGITS(CAST(SUBSTR(MAX(EMPNO),4,3) AS INT) + 1),6,5) 
        FROM EMPLOYEE1 WHERE EMPNO LIKE '0%');                   
---------+---------+---------+---------+---------+---------+-----
DSNE615I NUMBER OF ROWS AFFECTED IS 1                           
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0       


Regards
Raghu
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
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am
No new posts SQLCODE-000000080N priya91 DB2 1 Mon Feb 01, 2016 4:35 pm
No new posts Need to understand SQLCODE = 12 rohanthengal DB2 3 Thu Nov 05, 2015 11:22 am
No new posts SQLCODE -922 in Batch-Cics program tr... kishpra CICS 2 Thu Sep 10, 2015 12:36 pm
No new posts SQLCODE -450 for stored procedure spoorni DB2 3 Fri Jan 16, 2015 2:45 am


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