Case sensitivity in DB2/400 UDF?

Question

I am writing an SQL wrapper for a service program procedure. The procedure works, but the UDF I am creating is behaving strangely. When I create the UDF with the external name keyword, it is becoming case sensitive. Here is the code I have:

create function C1ANEWF.getSalesAuditStorePeriodLibrary
                   (inStore decimal(5,0),
                    inDate  date)
returns char(10) ccsid 37

language rpgle
parameter style general
specific sa1802f001
not deterministic
reads sql data
returns null on null input
not fenced
program type sub
no final call
allow parallel
no scratchpad
external name C1NEWO.SA1802("getSalesAuditStorePeriodLibrary")

When I create this, it works. If I remove the "" from the procedure name, it does not. I have lots of SQL UDFs where I am not using the "", and case sensitivity is not an issue.

Any ideas?

Answer 1

Display the procedure exports of your service program, with DSPSRVPGM ____ DETAIL(*PROCEXP). You will see that the procedure name is mixed case. Now look at one of your older service programs. You will see uppercase names.

The quotes your SQL make sure that mixed case is used, and thus match the procedure name, which is case sensitive. Without those quotes, the name would be translated to uppercase, which would match your older procedures.