TechTip: Testing Stored Procedures

[MCWebsite.Staff]

** This thread discusses the article: TechTip: Testing Stored Procedures **
** This thread discusses the Content article: TechTip: Testing Stored Procedures0

[mthoreb]

TechTip: Testing Stored Procedures

** This thread discusses the article: TechTip: Testing Stored Procedures **
Great introduction to a very useful component of iSeries Navigator. I've used Run SQL Scripts quite a bit to create many SQL stored procedures. One hidden gotcha in SQL stored procedures is the "resolution of unqualified objects" (i.e. tables without library qualifiers). Run SQL Scripts can control how the 400 resolves these unqualified objects. And this is one of the main differences between Native OS/400 environment (library lists) and the DB/2 environment (schemas). I wish IBM would make Run SQL Scripts into a stand alone product similar to some of the other DB manufacters' developer tools.

[Guest]

TechTip: Testing Stored Procedures

** This thread discusses the article: TechTip: Testing Stored Procedures **
Hi! When I try to run this for file ABCDEF or any other file I get the following message: Vendor Code: -204 Message: [SQL0204] ABCDEF in MDW type *FILE not found. Cause . . . . . : ABCDEF in MDW type *FILE was not found. If the member name is *ALL, the table is not partitioned. "MDW" is my user id and not the library where the file resides or where the stored procedure exists!! Any idea why it is using my user id as the library name for the table to query? Cheers, Martin

[mthoreb]

TechTip: Testing Stored Procedures

** This thread discusses the article: TechTip: Testing Stored Procedures **
It appears that you've uncovered the hidden gotcha. You are running in DB2 (database) mode and the 400 is trying to find the table in your schema - assumed to be your user profile MDW. Let's assume that your user profile has the file in your default library list. If you're using Run SQL Scripts, try the following within Run SQL Scripts: 1. Navigate menu Connection -> JDBC Setup 2. Click the [Format] tabl 3. Set the Naming Convention to [System] 4. After saving this change, recreate the SQL stored procedure You need to recreate the stored procedure, because the SQL/System Naming Convention setting is embedded into the compiled object. Try running the stored procedure again. Another big difference between SQL and System is the separator used when qualifying stuff. SQL uses the DOT while System uses the SLASH.

[Guest]

TechTip: Testing Stored Procedures

** This thread discusses the article: TechTip: Testing Stored Procedures **
Many thanks for your help, but it seems that I have problem with Ops. Nav. When I change to SYSTEM in the Format Tab and press the OK button nothing happens! I'm running V5R4 so it sounds like I need some PTF's. In the meantime is there any other way I can specify 'System'? Cheers, Martn

[mthoreb]

TechTip: Testing Stored Procedures

** This thread discusses the article: TechTip: Testing Stored Procedures **
That's a good one, I haven't encountered that before. A couple of things to consider - maybe you need administrator authority on your workstation to make preference changes (?), or you need a service pack for Navigator. Also, after making the change on the Format tab, try selecting another tab before clicking OK.

[Guest]

TechTip: Testing Stored Procedures

** This thread discusses the article: TechTip: Testing Stored Procedures **
I can make changes on PC, so I don't think that is the problem. Also, the OK button doesn't work on any of the tabs - Sounds like I need the latest service pack......Cheers.......Martin

[Guest]

TechTip: Testing Stored Procedures

** This thread discusses the article: TechTip: Testing Stored Procedures **
Hi! I applied the latest service pack and this works fine now. Thanks for your help. Cheers, Martin