Need help using a query rule with a DB2 database running on a Linux server.

Here are some related documents concerning Relational Databases and DB2

forum.appian.com/.../Configuring_Relational_Databases.html

In order to troubleshoot issues where the message is very generic such as: "Unexpected error executing query" you need to reproduce and then find this error in the application-server.log, once you have identified it review each of the individual "Caused by" clauses. Usually the most bottom ones (that belong to that error) will be the most informative ones in the stack trace.

You won't know what the problem is if you only focus the troubleshooting on the generic "Unexpected error executing query" error message.

Eduardo, after looking into the application log as per your suggestion, I did find the following: Caused by: java.sql.SQLSyntaxErrorException: [FMWGEN][DB2 JDBC Driver][DB2]APPIANDB.UPS_ORGHIERARCHY IS AN UNDEFINED NAME
APPIANDB is not defined in the CDT, only UPS_ORGHIERARCHY which is the view name.

Is APPIANDB a different schema from the one you use to connect Appian to this Database?

No but it's the ID i'm using in the weblogic data source. I have tried changing the schema name used in the CDT but it's not picking it up, it just defaults to what's listed above. What's odd is that I have no problem using the database node, the problem only occurs using the query rule

That has to do with the way the library used for query rules work. Here's what you need to do:

APPROACH #1:

1. Change the schema name that owns this view to be lowercase.
2. Edit your XSD to use the schema="" attribute and put the schema name in lower case

@Table(name="" schema="")

3. Delete the CDT
4. Re-upload the updated CDT
5. Run impact analysis to update your data store (assuming the Data store is inside an application)
6. Update the data store

APPROACH #2

If you cannot rename it to lowercase (which is the default behavior of Appian for DS validation) then you will have to do this:

In DB2:

1. Login as the SCHEMA who owns the view
2. Grant privileges over the VIEW to the Appian ID
3. Login as the Appian ID
4. Create a synonym where XXXXX is the Schema that owns the view


CREATE SYNONYM UPS_ORGHIERARCHY FOR XXXXX.UPS_ORGHIERARCHY;

5. Make sure your XSD has the following annotation WITHOUT ANYT SCHEMA NAME

@Table(name="UPS_ORGHIERARCHY ")

6. Delete the CDT
7. Re-upload the updated CDT
8. Run impact analysis to update your data store (assuming the Data store is inside an application)
9. Update the data store

Eduardo, approach 1, step 1: the schema name for the view is in the DB2 database. If this is what you mean, I will put in a request tomorrow to have it changed. Can you confirm my understand for this step is correct?

Thanks, Mario

Correct. The step #1 of approach #1 (rename the schema to lowercase) has to be done in DB2.

thank you Eduardo, I will follow up with the owner to have this changed and let you know how it works out.

Thanks again, Mario