By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

Problems querying tables in federated database

P: 1
Hi,

I have 2 servers each running AIX and both have a DB2 database setup on them. I'm building a news management application and in our setup we need to have a staging and production setup. So the staging database gets the changes performed and then once we're satisfied with how it looks, we push the changes through SQL queries which perform the (deletions, updating, and inserting of articles) onto the production database by comparing it to the staging database.

The problem is the database is federated and the nicknames are created on the staging database but I'm getting errors while running certain queries.

Below is how the databases are setup and how the wrapper was created for the federated database.

-------------------------------------------------
Database 1: STAGING
- Users: NEWSADM, SWGNEWSR, NEWSFED
Database 2: PRODUCTION
- Users: NEWSFED

AIX64/DB2 9.1
-------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. CONNECT TO STAGING user NEWSADM using 'p4ssw0rd';
  2.  
  3. CREATE WRAPPER MEGADBWR LIBRARY 'libdb2drda.a' OPTIONS (ADD DB2_FENCED 'N');
  4.  
  5. CREATE SERVER MEGADB11 TYPE DB2/UDB VERSION '9.1' WRAPPER MEGADBWR AUTHID "NEWSFED" PASSWORD "p4ssw0rd" OPTIONS (ADD DBNAME 'PRODNEWS', PASSWORD 'Y'); 
  6.  
  7. CREATE USER MAPPING FOR SWGNEWSR SERVER MEGADB11 OPTIONS (REMOTE_AUTHID 'NEWSFED', REMOTE_PASSWORD 'p4ssw0rd');
  8.  
  9. CREATE NICKNAME PRODNEWS.NEWS_ARTICLES FOR MEGADB11.SWGNEWS.NEWS_ARTICLES;
  10. CREATE NICKNAME PRODNEWS.NEWS_CATEGORIES FOR MEGADB11.SWGNEWS.NEWS_CATEGORIES;
  11. CREATE NICKNAME PRODNEWS.SITES FOR MEGADB11.SWGNEWS.SITES;
  12.  
  13. GRANT SELECT,INSERT,UPDATE,DELETE,CONTROL ON PRODNEWS.NEWS_ARTICLES TO USER NEWSFED;
  14. GRANT SELECT,INSERT,UPDATE,DELETE,CONTROL ON PRODNEWS.NEWS_CATEGORIES TO USER NEWSFED;
  15. GRANT SELECT,INSERT,UPDATE,DELETE,CONTROL ON PRODNEWS.SITES TO USER NEWSFED;
  16.  
  17. SELECT COUNT(*) FROM PRODNEWS.NEWS_ARTICLES;
  18. SELECT COUNT(*) FROM PRODNEWS.NEWS_CATEGORIES;
  19. SELECT COUNT(*) FROM PRODNEWS.SITES;
  20.  
  21. CONNECT RESET;
================================================

The first error that comes up is when I perform a select query on certain tables:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     *
  3. FROM
  4.     PRODNEWS.NEWS_CATEGORIES AS A
  5. WHERE
  6.     NOT EXISTS(
  7.         SELECT
  8.             CATEGORY_ID
  9.         FROM
  10.             SWGNEWS.NEWS_CATEGORIES AS B
  11.         WHERE
  12.             A.CATEGORY_ID = B.CATEGORY_ID
  13.     )
Gives me this error:
Warning: odbc_exec() [function.odbc-exec]: SQL error: [IBM][CLI Driver][DB2/AIX64] SQL30090N Operation invalid for application execution environment. Reason code = "22". SQLSTATE=25000 , SQL state 25000 in SQLExecDirect

The funny and weird thing is that I can perform this same query except on the NEWS_ARTICLES table and the query runs without any errors.

The second error is when I perform a DELETE:
Expand|Select|Wrap|Line Numbers
  1. DELETE FROM
  2.     PRODNEWS.NEWS_ARTICLES AS A
  3. WHERE
  4.     NOT EXISTS(
  5.         SELECT
  6.             NEWS_ID
  7.         FROM
  8.             SWGNEWS.NEWS_ARTICLES AS B
  9.         WHERE
  10.             A.NEWS_ID = B.NEWS_ID
  11.     )
Gives me this error:
SQL1822N Unexpected error code "-507" received from data source "MEGADB11".
Associated text and tokens are " SQL0507N The cursor specified in the UPDATE
or DELETE ". SQLSTATE=560BD

SQL1822N Unexpected error code "-507" received from data source "MEGADB11". Associated text and tokens are " SQL0507N The cursor specified in the UPDATE or DELETE ".
sqlcode: -1822

sqlstate: 560BD

The weird thing yet again is that this query can be performed on the NEWS_CATEGORIES table without any errors.

If anyone can provide guidance and help on debugging this issue, I would greatly appreciate any help as I have been working endlessly trying to find a solution and what's wrong with the database with no luck.

Thanks!
Sep 1 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.