Can someone explain why my company paid a decent amount of money for Rational for i, however in order to create SQL UDF's or stored procedures I still must resort to using the IBM i Navigator SQL scripting utility? I have tried everything including copying the Navigator's JDBC settings exactly and am unable to create UDFS in Rational. The same goes for Data Studio. Additionally, using naming = System one cannot see any tables. It seems like the abilities of the product are somewhat lacking considering STRSQL and Navigator work better.
What do people here use for SQL development? Am i missing some secret setup?
I feel a bit short changed that Rational is not able to handle such tasks.
This topic has been locked.
5 replies Latest Post - 2012-10-17T05:02:34Z by B.Hauser
Pinned topic Rational -- UDFs & Stored Procedures
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-10-17T05:02:34Z at 2012-10-17T05:02:34Z by B.Hauser
canutri 120000AW8Y1 PostACCEPTED ANSWER
Re: Rational -- UDFs & Stored Procedures2012-10-13T23:16:08Z in response to JamesStrickJames,
Setting up Rational to view DB2 for i database objects can be done, but it requires some obscure driver setup. You cannot use naming=sysem (AFAIK), so you must be willing to use the dot qualifier instead of the native slash qualifier for tables. This also means you cannot use library list (which is important in our environment as we have 5 libraries for our ERP application). Because of this we also define an additional driver & connection using naming=system.
Create a driver definition via Window>Preferences>Data Management>Connectivity>Driver Definitions
Click the Add button
From the New Driver Definition dialog, Vendor Filter select DB2 for i5/OS
Select Other Driver
Provide a meaningful name in Driver name (i.e. MegaERP)
Click the Jar List tab
Click the Add JAR/Zip button
Navigate to you jt400.jar location, click Open
Click the Properties tab
Enter Connection URL: jdbc:as400:<myIBMiDB>;prompt=false;)
Driver Class: com.ibm.as400.access.AS400JDBCDriver
User Id: ItsMe
Now with a properly defined driver you can create a Database Connection in the Data perspective.
From the Data Source Explorer view, Click the New Conneciton Profile button
Select DB2 for i5/OS in the database manager pane
Select the driver definition you created above from the JDBC Driver drop-down (all the normal driver properties should already be set)
Provide the User name and Password
Click the Save Password checkbox
Viola! you should now have a functioning Database Connction providing you with drill-down into the schema list and db objects.
For simple SELECTS, I may use the default SQL editor. However, when I really need the editor to be DB2 for i aware, I will use Navigators Run SQL Script. When using projects via Data Project Explorer (as we do with RTC), you can open the Run SQL Scripts editor by right-clicking the SQL script and select Open With>System Editor.
JamesStrick 270005RQ3U2 PostsACCEPTED ANSWER
Re: Rational -- UDFs & Stored Procedures2012-10-14T02:33:07Z in response to canutriI have the same setup, two different connections (SQL and SYS). I cannot use SQL to create functions and SPROCS as they ignore the library list which we utilize. However using the SYS connection i can never drill down into tables.
Currently i have to perform the following gymnastics to develop for SQL on i:
Edit in RDP so that i have some source highlighting (though it misses some statements and the verifier is useless). Once i have it edited i have to copy it over to Navigator's run SQL scripts to see if it will run. Once i have corrected any code errors and crated a procedure, then i must paste back into RDP so i can save the source off. It just seems ridiculous given that RDP costs $800. Can the RDP team not copy the drivers utilized by Navigator? Data Studio aslo seems to be not quite supported on IBM i which i find disappointing.
krmilligan 120000MDWP335 Posts
B.Hauser 1000007U1D218 PostsACCEPTED ANSWER
Re: Rational -- UDFs & Stored Procedures2012-10-17T05:02:34Z in response to krmilliganI couldn't find an option to switch the naming conventions in RDp either.
Unfortunately SET OPTION NAMING can only be specified within embedded SQL but is not allowed in an SQL function, SQL procedure, or SQL trigger.
Because we are working with System i applications, i.e. using a library list and group profiles, I develop all my SQL routines solely with System i Navigator.
May be we need to open an request at Rational to allow SQL AND System Naming