MVSFORUMS.com

[NASCAR9]

I need to write function in COBOL that will receive a value and return a different value. Does anyone have an example of a simple procedure/COBOL program similar to my requirements?
_________________
Thanks,
NASCAR9

[kolusu]

NASCAR9,

I beleive that there is no big difference between SP and UDF. Just code the UDF similar to an SP

Hope this helps...

Cheers

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

[agredell]

I was wondering if anyone has anything new to add to this thread? I have a working SP, and a UDF created on it, but it doesn't return anything when invoked through a function call, while the SP does return stuff. There is no real SQL executed in the SP, just some string manipulation, and the UDF response is zero bytes long. For example, when the SP is given an input of '100', it returns ' 00100'; but when invoked as a UDF by issuing "SELECT SANDBOX.PARTS2L('100') FROM SYSIBM.SYSDUMMY1", no output is returned.

Any help will be greatly appreciated!

This seems to be an area where the DB2 manuals are especially weak in their attempt to provide solutions!
_________________
Alan Gredell

[kolusu]

[jsharon1248]

What does this mean?

[agredell]

Sorry for any lack of clarity!!

Kolusu, I think I understand what you mean, and it will be possible for me to test this UDF using a value selected from a real table.

jsharon, I was using the statement in the reply to the original post, that there is no significant difference between a SP and a UDF; I took that to mean that I could define a UDF using the EXTERNAL_NAME of the SP, and we did get it to run that way, it just wouldn't return any results.

One thing that occurred to us is that the SP was coded with an INOUT field, where both the input and the output are using the same Linkage Section variable. The DBA who is responsible for the original SP is trying to add a second, output variable to the Linkage Section of the COBOL program. That might help, as might Kolusu's suggestion.

Thanks, I will keep you posted!
_________________
Alan Gredell

[agredell]

The changes we made worked! Kolusu, it appears not to matter that the SYSDUMMY1 table definition is not equal to the field or fields in question; for example, the following query:

select sandbox.parts2x('100') from sysibm.sysdummy1

worked perfectly, returning the value '00100', which is what we needed.

I believe that there are some differences regarding what will work in an SP and what will work in a UDF, but they are largely the same.

A UDF will require two fields (at a minimum), one for input, the other for output. They appear to require variable length fields to be used, though I wouldn't swear to that one; at any rate, it's working!

Thanks for helping me think this through!!!
_________________
Alan Gredell

[jsharon1248]

A couple things to point out. First, regarding SYSIBM.SYSDUMMY1, the table is defined with 1 column and populated with 1 row. The 1 row is the feature that makes the table valuable. You could select CURRENT TIMESTAMP from any table, but it will be returned once for every row on the table. Issuing the select to SYSIBM.SYDUMMY1 will return 1, and only 1 row. In your case, you're selecting the results of a UDF.

Second, regarding the similarities and differences between SP's and UDF's, they are similar from the external coding perspective. You pass them parms, and they do some work. To the DB they are very different. SP's are explicitly called using a CALL statement, and UDF's are implicitely invoked when the UDF name is encountered in SQL statements. UDF's do not require input parameters. The parameter types defined in the UDF must be compatible with the parameters defined in the external program. I hope you haven't defined the UDF and SP to both call the same COBOL pgm. If you need to execute the same program, define the SP to call the COBOL program, and issue a CALL to the SP from the UDF.

At this point, I'm curious what changes you made to resolve your issues.

[agredell]

I have used SYSIBM.SYSDUMMY1 for years in cases where I had a literal that needed a function of any kind applied. Normally, I will expect this UDF to reference a column in a table, rather than a literal, and therefore will reference that table instead of SYSIBM.SYSDUMMY1.

I guess the second paragraph of your post is stating that there is a difference between a UDF and the program that is excecuted due to the invoking of the function. That's a good point, it helps to keep them separated.

As far as what we had to do to make the SP work as a UDF, I didn't actually do it, but the SP was coded with a parameter type of INOUT; we could not get the UDF to work that way, so we modified the linkage section to have separate input and output variables. Also, the SP was capable of handling a fixed length character field, but we had to make it a VARCHAR to get the UDF to work.

Here is a synopsis of our requirement: Our company has two (maybe more, but two is one too many!) version of many, maybe all, product identifiers. I'm talking about what the user calls a product. The users that create the product names have put some "intelligence" into the number, but only in some cases. Both versions of the product number have a pattern XX99999XXX. There are numerous rules around the values allowed for the X's, but the first two characters are permitted to be blank, as are the last 3. Then, the middle 5 have just numerics. But the "official" (we call it the long) part number might have leading zeros (and this is true even if the first two characters are NOT blanks). Some sample long part numbers are: < 00100 >, <DL01234M05>, <DR09451 15>, <A 00001N01> (Note that the <> are delimiters for clarity)

Our sales force consist of independent agents; and the programmers that built their system were an independent bunch, too! They analyzed the data in these fields and figured that they could save keystrokes for the sales force by shortening the part number by eliminated leading spaces and leading zeros in the numeric portion of the number. So, the short versions of the above numbers would be (still always 10 characters!): <100 >, <DL1234M05 >, <DR9451 15 >, <A1N01 >.

Finally, a new application started out by using the wrong table to verify part numbers. The table they used stored the short number, but was an incomplete list. The correct table with all current values only stores the long number. Hence, we decided that a UDF would help us go either way.

Long story short (oops, too late! ), we got both of them working today. It's not the smartest thing to do, but my company has a habit of not always doing the smart thing (or else we wouldn't have two versions of part numbers!!!)

Thanks for helping me think this through, guys!
_________________
Alan Gredell