MVSFORUMS.com

Cogito-Ergo-Sum

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.

kolusu · Posted: Thu May 26, 2005 7:48 am Post subject:

Cogito-ergo-sum,

What is the exact error sql code/message ?

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

Cogito-Ergo-Sum · Posted: Thu May 26, 2005 8:03 am Post subject:

-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.

kolusu · Posted: Thu May 26, 2005 8:09 am Post subject:

Cogito-Ergo-Sum,

Try this

schintala · Posted: Thu May 26, 2005 8:10 am Post subject:

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.

Cogito-Ergo-Sum · Posted: Thu May 26, 2005 8:48 am Post subject:

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.

schintala · Posted: Thu May 26, 2005 9:03 am Post subject:

It looks to me missing the length of variable in RETURN clause.

Cogito-Ergo-Sum · Posted: Thu May 26, 2005 9:11 am Post subject:

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,

schintala · Posted: Thu May 26, 2005 9:29 am Post subject:

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 ;

Cogito-Ergo-Sum · Posted: Thu May 26, 2005 9:39 am Post subject:

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.

Cogito-Ergo-Sum · Posted: Thu May 26, 2005 9:46 am Post subject:

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

kolusu · Posted: Thu May 26, 2005 9:50 am Post subject:

schintala · Posted: Thu May 26, 2005 10:08 am Post subject:

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.