Post by Tatsiana
I have one question that is related to using custom SQLs.
Could you please clarify whether it's possible to replace the select,add,update while db2 connector with Merge statement?
I suppose it will bring a lot of performane win in our project.
Thanks in advance.
The simplest approach is to do this entirely from script. If you look up the JDBCConnector (or DatabaseConnector) in the JavaDocs then you'll see the execSQL() method:
Of course all the Java docs are installed with TDI (SDI) under the <Install_dir>/docs/api folder.
The execSQL() method lets you execute any kind of SQL or DDL statement, including creating and dropping tables, adding indexes and doing as complex joins as you please.
In order to get hold of an initialized JDBC Connector you have a couple of choices:
1. Add and configure the Connector to your AL and set its State to Passive. This means it gets initialized and shutdown as the AL starts and stops, but it is never used by built-in AL processing logic. Instead, in the Prolog - After Initialize Hook you do this:
jdbc = thisConnector.connector;
and after this point you can call jdbc.execSQL()
2. Add and configure the Connector in your Resources > Connectors library folder. Then you can load it from script and initialize it yourself:
jdbc = system.getConnector("MyLibraryDBConnector");
Again you are ready to call any its methods.
3. Load the basic Connector and set the parameters yourself, using the 'internal name' for each param as show in the Parameter Dialog (what you get when you click on the label for a parameter, or on the little pencil button out to the right):
jdbc = system.getConnector("ibmdi.JDBC");
And away you go.
Note the that return value from execSQL() is a string that is either empty if all went well, or holding the error returned from the database server.
Also note that if you want to read and return entries, use execSQLSelect() instead followed by repeated calls to getNextSQLSelectEntry() to retrieve entries.