Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
Replies
9 replies
Subscribers
7 subscribers
Views
2423 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
AI and Rules
Need help using a query rule with a DB2 database running on a Linux server.
mariod
over 9 years ago
Need help using a query rule with a DB2 database running on a Linux server.
we are using a DB2 database for the first time and while testing the query rule, the following error is displayed:
There was an error testing the rule:
Expression evaluation error in rule 'testsccdrule' at function 'queryruleexec': Error evaluating function 'queryruleexec' : Unexpected error executing query (type: [SCCDDBHIERARCHYALLDT5651], query: [testSCCDRule], order by: [[Sort[hierarchyDescription asc]]], filters:[null])
I can query the view using the database node but I cannot get a CDT to work using a query rule.
The view only has one varchar field and we are on Appian v7.3
...
OriginalPostID-123789
OriginalPostID-123789
Discussion posts and replies are publicly visible
0
Tim Nguyen
Appian Employee
over 9 years ago
Here are some related documents concerning Relational Databases and DB2
forum.appian.com/.../Configuring_Relational_Databases.html
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Eduardo Fuentes
Appian Employee
over 9 years ago
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.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
mariod
over 9 years ago
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.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Eduardo Fuentes
Appian Employee
over 9 years ago
Is APPIANDB a different schema from the one you use to connect Appian to this Database?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
mariod
over 9 years ago
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
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Eduardo Fuentes
Appian Employee
over 9 years ago
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
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
mariod
over 9 years ago
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
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Eduardo Fuentes
Appian Employee
over 9 years ago
Correct. The step #1 of approach #1 (rename the schema to lowercase) has to be done in DB2.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
mariod
over 9 years ago
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
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel