[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 776
  • Last Modified:

call a user defined function in db2

I created a UDF in DB2,
with operation navigator (v5r4m0)
now: How can I have to do in order to use this function in my sql codes?
thanks
0
bobdylan75
Asked:
bobdylan75
  • 6
  • 5
  • 4
  • +1
2 Solutions
 
momi_sabagCommented:
once the user defined function is created, you just reference it in your code just like any other function
0
 
bobdylan75Author Commented:
in this way?
select dbo.functionName(table1.field1) from dbo.table1
?
because my v5r4m0 says that functionName doesnt exist
0
 
momi_sabagCommented:
are you sure it is in the schema dbo?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
bobdylan75Author Commented:
yes.. (the real name is qs36f but in this post i want to semplify)
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

I could be wrong, but I don't believe you can specify the schema when referencing a user-defined function. It must be located in the library-list somewhere.

I wrote a function called IsNumeric, and I can call it like this

HTH,
DaveSlash

select IsNumeric('123')
  from sysibm.sysdummy1

Open in new window

0
 
tliottaCommented:
It should be possible to qualify the function name with a schema name, though it never crossed my mind to do so. I've never seen it done in the AS/400 line.

Because I've never seen it done, my first question would be about syntax, specifically due to naming convention. I would assume that this is valid with a couple assumptions:
select dbo.functionName(table1.field1) from dbo.table1

Open in new window

First assumption is that *SQL naming is in effect. And of course, dbo is a valid schema, and table1 and field1 are valid. The function functionname should exist in dbo.

After that, the specific item to verify is that the data type of table1.field1 matches the expected data type of the first and only input parameter of dbo.functionname.

We'd need to see the definition of table1.field1 and of the dbo.functionname parameter list to know any more.

Tom
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Good point, Tom. In *SQL naming convention, I can successfully qualify the UDF with a schema. That doesn't appear to work in *SYS naming convention.

e.g.

select MySchema.IsNumeric(deleteme.field2)
  from SomeSchema.deleteme

==> in *SQL naming convention, this works

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

in *SYS naming convention, this works:

select IsNumeric(deleteme.field2),
       deleteme.field2
  from SomeSchema/deleteme

but this doesn't:

select MySchema/IsNumeric(deleteme.field2),
       deleteme.field2
  from SomeSchema/deleteme

Column MYSCHEMA not in specified tables.

Open in new window

0
 
tliottaCommented:
Try MySchema.IsNumeric(deleteme.field2) even under *SYS naming.

I should have clarified that the assumption of *SQL naming specifically referred to the "dbo.table1" element. Even under *SYS naming, the qualification of column names is done with a ".dot" delimiter. I would expect similar behavior for function qualification -- though I don't have an example available right now.

Tom
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Thanks Tom. You're correct in that the qualification of column names is done with the dot delimeter. But, it doesn't work for me to qualify the function in *SYS mode.

e.g.

select MySchema.IsNumeric(field2)
  from SomeSchema/deleteme

Qualified object name ISNUMERIC not valid.

select MySchema/IsNumeric(field2)
  from SomeSchema/deleteme

Column MYSCHEMA not in specified tables.

Open in new window

0
 
bobdylan75Author Commented:
If I use dbo/ before functionName,
the machine says: dbo not found
if I dont put dbo, it says: qualifier functionName doesnt exist..
Is it a possibile error of ptf at this point???
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
It's probably not a PTF problem, but I never rule out that kind of thing. What happens if you use the dot qualifier using *SQL naming-convention?

Also, are you sure the function exists in qs36f. (We should stop using the term "dbo" since that's specific to MS SQL Server.)

-- DaveSlash
0
 
tliottaCommented:
Yes, if the schema isn't "dbo", then "dbo" shouldn't be used at all. If the schema (the 'library') is QS36F, then QS36F should be used.

From the V5R4 SQL Reference under the 'Function Resolution' sub-topic:

A function is invoked by its function name, which is implicitly or explicitly qualified with a schema name, followed by parentheses that enclose the arguments to the function.

And later:

Qualified function resolution: When a function is invoked with a function name and a schema name, the database manager only searches the specified schema to resolve which function to execute.

Then comes an example under the 'Determining the best fit' sub-topic:

Also assume that a function with three arguments of data types VARCHAR(10), SMALLINT, and DECIMAL is invoked with a qualified name:
MYSCHEMA.FUNA( VARCHARCOL, SMALLINTCOL, DECIMALCOL ) ...

It might be a PTF issue. Or it might simply be that QS36F isn't being used as the qualifier. Or it might be that QS36F isn't a valid "schema" reference.

Tom
0
 
bobdylan75Author Commented:
I tried to put the function in QGPL schema.
It's the same...
Perhaps my function syntax is incorrect even if it doesnt report to me?
0
 
tliottaCommented:
This was mentioned earlier:

After that, the specific item to verify is that the data type of table1.field1 matches the expected data type of the first and only input parameter of dbo.functionname.

We'd need to see the definition of table1.field1 and of the dbo.functionname parameter list to know any more.

Until we are shown those, there is no more we can say except random guesses. We can't help in locating problems without seeing the definitions.

Tom
0
 
bobdylan75Author Commented:
Sorry,
I understood that the issue was on operation navigator,
infact if I create a function from prompt I have no problems.
0
 
bobdylan75Author Commented:
thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now