#Sharing - Access AS400 DB2 Here I want to sharing steps for access A

heriz over 8 years ago

#Sharing - Access AS400 DB2

Here I want to sharing steps for access AS400 DB2 Database from Appian Query Database Smart Service.

Steps:
1. Create a folder <JBOSS_HOME>\\modules\\com\\ibm\\as400\\main. Copy AS400 DB2 driver ('jt400.jar') and file module.xml (attached).
2. Update driver datasources list in 'standalone.xml' file :
<driver name="as400" module="com.ibm.as400">
<driver-class>com.ibm.as400.access.AS400JDBCDriver</driver-class>
<xa-datasource-class>com.ibm.as400.access.AS400JDBCXADataSource</xa-datasource-class>
</driver>
3. Add datasource in standalone.xml between "<subsystem xmlns="urn:jboss:domain:datasources:1.2">". My script like this (or you can add the datasource in Jboss Management Console):
<datasource jta="true" jndi-name="java:/jdbc/as400" pool-name="AS400" enabled="true" use-ccm="true" statistics-enabled="false">
<connection-url>jdbc:as400://HOSTNAME</connection-url> 
<driver-class>com.ibm.as40...

module.xml

Discussion posts and replies are publicly visible

0 heriz over 8 years ago

...0.access.AS400JDBCDriver</driver-class>
<driver>as400</driver>
<pool>
<max-pool-size>1000</max-pool-size>
</pool>
<security>
<user-name>USERNAME</user-name> 
<password>PASSWORD</password> 
</security>
<validation>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
</validation>
<timeout>
<set-tx-query-timeout>false</set-tx-query-timeout>
<blocking-timeout-millis>0</blocking-timeout-millis>
<idle-timeout-minutes>300000</idle-timeout-minutes>
<query-timeout>0</query-timeout>
<use-try-lock>0</use-try-lock>
<allocation-retry>0</allocation-retry>
<allocation-retry-wait-millis>0</allocation-retry-wait-millis>
</timeout>
<statement>
<share-prepared-statements>false</share-prepared-statements>
</statement>
</datasource>
4. Add the new JNDI Name (ex: java:/jdbc/as400) in 'conf.data.datasources' on 'custom.properties' file.
5. After this, w...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 heriz over 8 years ago

...e can try accessing to AS400 from Query Database Smart Service from 'Pre-Defined Datasource' by select the JNDI name.

Hope this helps you all that found the same issue from accessing to AS400 DB2 (not IBM DB2 UDB).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
A Score Level 1
over 8 years ago

What version is this for? Step 4 might be different now, given recent changes to the configuration files?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 heriz over 8 years ago

Hi, the steps above tested and worked in AS400 version V5R4M0
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
A Score Level 1
over 8 years ago

I meant Appian version :)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 heriz over 8 years ago

The Appian version is 16.1
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
A Score Level 1
over 8 years ago

Out of interest, did you test configuring the datasource in the administration console as described here? I believe that's now the preferred way of configuring business data sources rather than adding them to custom.properties. forum.appian.com/.../Configuring_Relational_Databases.html
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 heriz over 8 years ago

Nope, we don't have option adding AS400 datasource in Admin console. The listed datasource limited only to DB2, MYSQL, ORACLE, MySQL. And DB2 from Appian list is for IBM DB2 UDB (not for IBM iSeries AS400).

One we need to know is, connection string for AS400 DB2 is : jdbc:as400://HOSTNAME (not jdbc:db2://HOSTNAME). Appian will show invalid connection string if we using jdbc:as400 instead of jdbc:db2 in DB2 in Admin console.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
A Score Level 1
over 8 years ago

That's good to know - I guess this will work until modifying the custom.properties file is deprecated :)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel