CREATE FUNCTION but can't find *SRVPGM

[cjkoontz]

This is weird.

I created the following function in STRSQL:

CREATE FUNCTION PGMRUTOOL/BBFLSTSTS (APPYER DECIMAL(2,0),
APPNBR DECIMAL(8,0))
RETURNS CHAR(60)
LANGUAGE RPGLE
NOT DETERMINISTIC
EXTERNAL NAME 'PGMRUTOOL/BBRLSTSTS(BBPLSTSTS)'
PARAMETER STYLE GENERAL

Nicely, I get this message:

CREATE FUNCTION statement complete

I am able to run the function in STRSQL and get the desired result. But when I search for the object, using --

WRKOBJ PGMRUTOOL/BBFLSTSTS

- or even -

WRKOBJ *ALL/BBFLSTSTS

The *SRVPGM does not exist.

 

[Mercury2]

.
WRKOBJ PGMRUTOOL/BBRLSTSTS should work much better.

Are you sure of the service program entry point ?

Excerpt from the SQL DB2 redbook

- For ILE service programs, the external program name is
schema-name/service-programname(entry-point-name).

- For Java programs, the external
program name is an optional jar-id followed by a fully-qualified-classname!method-name or fully-qualified-class-name.method-name.

- For all other languages, the external program name is schema-name/program-name.
.

 

[cjkoontz]

Thanks for the reply.

I think my WRKOBJ logic was right. I created a few other SQL functions that were SQL-based and the objects that were created were service programs based on the name used on the CREATE FUNCTION command.

I'm pretty sure the entry point is correct. I mean, would the function work at all (with STRSQL) if it wasn't?

I talked to IBM about this, yesterday, because I was racking my head, and they indicated that --
* Functions based on "Language SQL" will create service programs of type CLE.
* Functions based on "Language RPGLE" will somehow be wrapped up in the object named in the "EXTERNAL NAME" option.

They did point me to using --

SET CURRENT FUNCTION PATH = library_of_function_in_question

(Within the iSeries Access SQL script runner, at least) To allow the function to return its values.

They also said, "Go read the redbook 'Stored Procedures, Triggers, ...".

But it was this contributing writer from itjungle.com who told me what to try with the iSeries Access ODBC Driver:

* Server tab.
* In the Default Library field: enter the library name you use the with current function path statement.
* I then blanked-out everything else in the library list field, below it.

* Saved the settings.
* Ran a simple select with the view (and the function in question) in Query Analyzer, and the function started returning the values I wanted to see.

 

[Mercury2]

No misunderstanding pls. I just warned you to pay attention on the indicated entry point, I didn't mean that your logic or whatever was wrong.

As to wrkobj, what did WRKOBJ PGMRUTOOL/BB R LSTSTS say ?

The external name is the name of the program that the function will call when used on an sql stm.

 

[cjkoontz]

I know. I value your opinions. Just pointing out what I did.

wrkobj pgmrutool/bbrlststs shows --

Object Type Library Attribute
BBRLSTSTS *SRVPGM PGMRUTOOL RPGLE
BBRLSTSTS *MODULE PGMRUTOOL RPGLE

Which I created with CRTSQLRPGI (for the *module) and CRTSRVPGM (for the *srvpgm).