MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

CREATE FUNCTION erroring for invalid column type.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Cogito-Ergo-Sum
Advanced


Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA

PostPosted: Thu May 26, 2005 7:10 am    Post subject: CREATE FUNCTION erroring for invalid column type. Reply with quote

Hi,
I am completely confused about the CREATE FUNCTION command. No matter what combination I try, I always get a message as 'Column definition problem'. This is when I am trying from QMF.
Code:

CREATE FUNCTION XXXXXX.UDF1 (DOUBLE, VARCHAR(256), INTEGER)
RETURNS VARCHAR                                                   
EXTERNAL  NAME 'UDF1'                                             
LANGUAGE  ASSEMBLE                                               
DETERMINISTIC                                                     
RETURNS NULL ON NULL INPUT                                       
STAY RESIDENT YES                                                 
PROGRAM TYPE MAIN                                                 
NO EXTERNAL ACTION ;                                             

Can someone tell me what is the offending column? And, why is there a problem with the above column definition (when I am following what is specified in the manual)?

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ11/5.26?DT=20040921135507[/code]
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Back to top
View user's profile Send private message  
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose

PostPosted: Thu May 26, 2005 7:48 am    Post subject: Reply with quote

Cogito-ergo-sum,

What is the exact error sql code/message ?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website  
Cogito-Ergo-Sum
Advanced


Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA

PostPosted: Thu May 26, 2005 8:03 am    Post subject: Reply with quote

-604

I am running the above query in QMF and I get a message saying, 'Column definition problem.' When I hit PF1, I get the message of SQLCODE = -604
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Back to top
View user's profile Send private message  
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose

PostPosted: Thu May 26, 2005 8:09 am    Post subject: Reply with quote

Cogito-Ergo-Sum,

Try this

Code:

CREATE FUNCTION XXXXXX.UDF1 (DOUBLE, VARCHAR(256), INTEGER)         
    RETURNS VARCHAR                                         
    EXTERNAL  NAME 'UDF1'                                   
    LANGUAGE  ASSEMBLE                                       
    DETERMINISTIC                                           
    NO  SQL                                                 
    FENCED                                                   
    PARAMETER  STYLE  DB2SQL                                 
    RETURNS NULL ON NULL INPUT                               
    STAY RESIDENT YES                                       
    PROGRAM TYPE MAIN                                       
    NO EXTERNAL ACTION ;                                     

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website  
schintala
Beginner


Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA

PostPosted: Thu May 26, 2005 8:10 am    Post subject: Reply with quote

SQL 604 says the following. Looks like some thing wrong in the column definition length.

-604 A DATA TYPE DEFINITION SPECIFIES AN INVALID LENGTH, PRECISION, OR
SCALE ATTRIBUTE

Explanation: A data type definition in a CREATE or ALTER statement
contains an invalid length, precision, or scale attribute specification.
In addition, the specification of data type might be incorrect or invalid.
Or, the column definition in a view referenced in a CREATE TABLE LIKE view
has an invalid length.
Back to top
View user's profile Send private message  
Cogito-Ergo-Sum
Advanced


Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA

PostPosted: Thu May 26, 2005 8:48 am    Post subject: Reply with quote

No, Kolusu. I ran your query in QMF and through job step. Still the same SQLCODE.

Crying or Very sad
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Back to top
View user's profile Send private message  
schintala
Beginner


Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA

PostPosted: Thu May 26, 2005 9:03 am    Post subject: Reply with quote

It looks to me missing the length of variable in RETURN clause.
Back to top
View user's profile Send private message  
Cogito-Ergo-Sum
Advanced


Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA

PostPosted: Thu May 26, 2005 9:11 am    Post subject: Reply with quote

schintala,
I tried that too. Now, QMF says, 'Sorry, a system error occurred. Your command may not have been executed.' !

F1 shows me the SQLCODE as -20071 !!

When I run it through job step, I get,
Code:

DSNT408I SQLCODE = -20071, ERROR:  WLM ENVIRONMENT NAME MUST BE SPECIFIED XXXXXXX.UDF1
DSNT418I SQLSTATE   = 53099 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNXIVRO SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 150  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION           


Now to fix the above error....

The strange thing is, I tried the example from the manual link that I have posted above. That did not work too.
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Back to top
View user's profile Send private message  
schintala
Beginner


Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA

PostPosted: Thu May 26, 2005 9:29 am    Post subject: Reply with quote

I was able to run and created a row in our test DB. An entry has been created in sysibm.sysroutines for the UDF.

CREATE FUNCTION TESTF (DOUBLE, VARCHAR(256), INTEGER)
RETURNS VARCHAR(256)
EXTERNAL NAME 'TESTF'
LANGUAGE ASSEMBLE
DETERMINISTIC
NO SQL
FENCED
PARAMETER STYLE DB2SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION ;
Back to top
View user's profile Send private message  
Cogito-Ergo-Sum
Advanced


Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA

PostPosted: Thu May 26, 2005 9:39 am    Post subject: Reply with quote

schintala,
I do not know why, but, only after giving the WLM environment name, I could get the CREATE FUNCTION executed successfully.

Will experiment more...
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Back to top
View user's profile Send private message  
Cogito-Ergo-Sum
Advanced


Joined: 15 Dec 2002
Posts: 637
Topics: 43
Location: Bengaluru, INDIA

PostPosted: Thu May 26, 2005 9:46 am    Post subject: Reply with quote

schintala,
One last question.

Where do we specify the PDS name that holds the load module name that is the UDF?

I was running my UDF thru" job step on SYSIBM.SYSDUMMY1. So, I gave my PDS name in STEPLIB. Yet, the query returns
Code:

DSNT408I SQLCODE = -444, ERROR:  USER PROGRAM UDF1 COULD NOT BE FOUND


I checked the manual again and it does not specify where we provide the PDS name.
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Back to top
View user's profile Send private message  
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose

PostPosted: Thu May 26, 2005 9:50 am    Post subject: Reply with quote

Quote:

Where do we specify the PDS name that holds the load module name that is the UDF?


Cogito,

Look up LOADMOD column of the SYSIBM.SYSPROCEUDURES catalog table.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu


Last edited by kolusu on Thu May 26, 2005 11:47 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website  
schintala
Beginner


Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA

PostPosted: Thu May 26, 2005 10:08 am    Post subject: Reply with quote

SYSIBM.SYSROUTINES only for the registration of Sps and UDFs. You need to compile your program and need to place into the library. This library must be concatenated with the WLM address space JCL. You will have to talk to your sysadmn how these things set up in your shop.
Back to top
View user's profile Send private message  
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group