Driver and Metadata Ingestion for IBM AS400 iSeries

Have you tried connecting yourself to the database with a SQL editor?
That’s usually the first step to confirm everything is working. Have you pinged the database from the jobserver? That would rule out any firewall, etc.

I can connect with DBeaver to the database. I have not tried from the JobServer though, that’s a very good point and idea. I will give that a try today.

Thanks Arthur.

@justin.swenson.subzero.com

It seems like you are using right driver class. What did you input in connection string? Which driver are using to connect? May be driver doesn’t have this class.

You can download driver jar file to your system and do ‘java tvf driverFileName’ and that should display classes under it. Please check if you see something like com/ibm/as400/access/AS400JDBCDriver. Thanks!

@arvind.singh

Input String (Connection): jdbc:as400://{host};[libraries={database};]
Driver Class Name: com.ibm.as400.access.AS400JDBCDriver

jt400.jar is what I’m uploading into the driver I’m creating. I’m using that driver when attempting to connect.

The error is saying that this file doesn’t have the driver, but this driver is specifically made for AS400 and I know it works when using other tools like DBeaver.

I’m unsure about your method for checking driver classes inside of the .jar file.

@arthur.burkhardt

Thanks, Arthur. It looks like I was correct in guessing the -tvf statement @arvind.singh was suggesting was a cmd command.

Unfortunately, I’m getting a “-tfv command not recognized” error. I’ll give it another shot.

I just downloaded the Java Decompiler instead. I can see the class. You’re suggesting there’s case sensitivity in Collibra?

Actually, this is what I wanted to show. So the correct class exists and my case sensitivity is correct.

“Class not found” usually occurs when driver class is not correctly specified. But, I don’t think that’s the issue based on information you have shared. You can reach out to support and see if they can hook you up with custom CDATA driver for AS400. With CDATA drivers, you should be able to get detailed log in case of error.

I’ve been working with Collibra Support as well as a Collibra Coach. Here’s where we are at now.

I’m able to successfully connect, but no matter how we update the connection string and properties, it won’t identify the schemas (libraries).

If I make a DBever connection, here’s the connection string:
Connection: jdbc:as400://{host};[libraries={database};]
Driver Class Name: com.ibm.as400.access.AS400JDBCDriver

To successfully connect to Collibra:
Connection: jdbc:as400://$host;[libraries=$schema;]
Driver Class Name: com.ibm.as400.access.AS400JDBCDriver

We discovered a few things:

1. Using curly brackets {} breaks the connection.
2. It will not catch the schema/libraries. We have tried double brackets [[ ]], parenthesis ( ), no brackets, and putting the property information directly into the connection string.

None of those break the connection, but none of those successfully detected the schema.

So we are beyond the class error. The class error seemed to be 100% related to using the curly brackets. Now we need to get it to recognize even one schema.

I forgot to update everyone on the solution here.

Connection String
jdbc:as400://host;libraries=schema;translate binary=true;metadata source=0

Driver Class
com.ibm.as400.access.AS400JDBCDriver

The solution was the ending section. The JT400 driver needs the ending two portions in the connection string to work.

You can see this if you connect through a tool like DBeaver. When you check the driver properties it puts in bold which properties it is using to make the connection.

I’ve been told we are one of the first Collibra customers to get an AS400 metadata connector working without an API or third-party connector. Not sure if that’s correct or not. But hopefully, this can help others looking to connect to AS400.

Hi @justin.swenson.subzero.com ,
Thanks for posting the solution on it. We were able to use the JT400 driver as well, though it doesnt pull the source description of the tables and columns for our AS400. Instead we used DSPFFD and parsed the output into an import file.