JR28217: CREATE FEDERATED STORED PROCEDURE RETURNS SQL0204N FOR MS SQL SERVER 2005 IF REMOTE STORED PROCEDURE IS A DEFINED SCHEMA.

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• Create federated stored procedure returns SQL0204N for MS SQL
  server 2005 if remote stored procedure in a defined schema
  versus schema associated with user.
  
  Reproduce instructions
  connect to test;
  create wrapper trusted_ms library 'libdb2mssql3.a' options
  (DB2_FENCED 'N');
  create server <servername> type mssqlserver version 2005 wrapper
  trusted_ms options (node '<nodename>', dbname <dbname>;
  create user mapping for user server <servername>
  options(remote_authid '<authid>', remote_password '<password>');
  
  SET Passthru <servername>;
  ***Following commands executed MSSQL datasource***
  Drop schema [<schemaname>];
  CREATE SCHEMA [s<chemaname>] AUTHORIZATION [dbo];
  DROP PROCEDURE [<schemaname>].[<procedurename>];
  CREATE PROCEDURE [<schemaname>].[<procedurename>]
  
  <insert procedure code here>
  
  SET PASSTHRU RESET;
  
  ***Following commands executed on federarted database
  
  DROP PROCEDURE <federated schema>.<federated procedure>;
  --The following stmt report SQL0204N error
  CREATE PROCEDURE <"federated schema">.<"federated procedure">
  SOURCE "<datasource schema>"."<datasource procedure>" NUMBER OF
  PARAMETERS 14
  FOR SERVER <server> MODIFIES SQL DATA NOT DETERMINISTIC EXTERNAL
  ACTION ;
  

Local fix

Problem summary

• Users affected: Users of the MS SQL Server wrapper in WebSphere
  Federation Server
  
  Problem description and summary:
  See error description.
  

Problem conclusion

• Problem was first fixed in Version 9, FixPak 5 (s080512).  This
  fix should be applied on the federation server.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  JR28187

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  CONTROL CENTER

• Fixed component ID

  5724N9704

Applicable component levels

• R910 PSN

     UP

• R911 PSN

     UP

• R950 PSN

     UP