Discussion:
Use merge instead of update
(too old to reply)
Tatsiana
2019-02-18 14:57:36 UTC
Permalink
Hi all,
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.


____________
Best regards,
Tatsiana
Eddie Hartman
2019-02-19 08:25:37 UTC
Permalink
Post by Tatsiana
Hi all,
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.
____________
Best regards,
Tatsiana
Hi Tatsiana,

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:

https://www.stephen-swann.co.uk/javadoc/tdi7.1.1/com/ibm/di/connector/JDBCConnector.html#execSQL(java.lang.String)

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");
jdbc.initialize(null);

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");
jdbc.setParam("jdbcSource", "jdbc:...");
jdbc.setParam("jdbcLogin", "db2admin");
....
jdbc.initialize(null);

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.

/Eddie
Tatsiana
2019-02-20 10:25:48 UTC
Permalink
Hi Eddie,
Thanks a lot for your reply.

Second variant worked great for me.

And one more question,
is it more sufficient to create custom connectors instead of default ones.
Or it's not the place where I could get much performance win?

Thanks in advance.


_____________
Best regards,
Tatsiana
Eddie Hartman
2019-02-21 09:27:37 UTC
Permalink
Post by Tatsiana
Hi Eddie,
Thanks a lot for your reply.
Second variant worked great for me.
And one more question,
is it more sufficient to create custom connectors instead of default ones.
Or it's not the place where I could get much performance win?
Thanks in advance.
_____________
Best regards,
Tatsiana
I often choose the second method too :) Either way is just as efficient, although use pre-configured components from the Resources library can minimize scripting.

Note that if you drop connectors into the AL in Passive State, then in the Prolog - After Init Hook you can grab either the AL Connector (with AttMaps, Hooks, Auto-Reconnect/Failover, etc.) or the Connector Interface (LDAP, JDBC, MQ, ...) - or both.

If I need a number of connectors then I might be tempted to capture settings in a JS object:

connectorDetails = {
DB2_HR: {
type: "ibmdi.JDBC",
iterator: true,
params: {
jdbcSource: "jdbc://...",
jdbcLogin: "db2admin",
...
}
},
EntDirectory: {
type: "ibmdi.LDAP",
params: {
ldapUrl: "ldap://...",
ldapSearchBase: "ou=Org,...",
ldapSearchFilter: "mail=*acme.com",
...
}
},
...
}

Then I can programmatically set up a collection of connectors:

var cc = {}; // empty collection
for (name in connectorDetails) {
cDetails = connectorDetails[name];
iterator = cDetails.iterator || false; // default false

ctr = system.getConnector(cDetails.type),

for (pname in cDetails.params) {
pvalue = params[pname];
ctr.setParam(pname, value);
}

ctr.initialize(null);
if (iterator) {
ctr.selectEntries();
}
}

/Eddie

Loading...