检测和使用 DB2 v8 中的当前模式
我在 iSeries DB2 v8 中有一个非常大的存储过程，它执行以下操作：
- 调用同一模式内的其他存储过程
- 从字符串准备动态 sql 语句并运行它们
- 从同一模式调用其他函数
- 使用同一模式中的各种表
我的问题是该存储过程和附带的函数可能会从该模式更改为另一个模式（即从“superlib”更改为“restorelib”），并且整个代码当前已硬编码为与指定模式一起运行。
我想要的是能够执行以下两项操作之一：要么通过参数将所有内容所在的模式名称传递给存储过程，要么让存储过程检测模式名称并使用它来运行自身。
这是我当前代码的示例：
SELECT COUNT(*) INTO TotalNotDone FROM superlib.PROCESSTABLES WHERE PROCESS_FLAG < 1;
WHILE TotalNotDone > 0 DO
SET SQLLOOPSTMT = 'select name_to_proces from ' CONCAT SOURCELIBRARY CONCAT '.processtables where process_flag = 0' ;
PREPARE LOOPSTMT FROM SQLLOOPSTMT ;
OPEN LOOPCUR ;
FETCH LOOPCUR INTO TABLETOPROCESS ;
CALL superlib.SP_RESTORE_INSERTS ( SOURCELIBRARY , DESTLIBRARY , TABLETOPROCESS, P_STARTTIME ) ;
CLOSE LOOPCUR;
SELECT COUNT(*) INTO TotalNotDone FROM superlib.PROCESSTABLES WHERE PROCESS_FLAG < 1;
END WHILE ;
我想要的是 NOT 必须在存储过程中编写 superlib 来调用或引用我正在使用的表，并且只需让存储过程识别它当前位于模式 superlib 中。
我尝试过 SET CURRENT SCHEMA = 'SUPERLIB'; 和 SET SCHEMA = 'SUPERLIB'; 但在调用 TABLES 时都不起作用。
我还在创建存储过程时将路径从：更改
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","PROGUSER1" ;
为
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","SUPERLIB" ;
，但这显然没有任何作用。
I have a very big Stored Procedure in iSeries DB2 v8 which does the following:
- Calls other stored procedures inside the same schema
- Prepares dynamic sql statments from strings and runs them
- Calls other functions from the same schema
- Uses various tables from the same schema
My problem is that this Stored Procedure and the accompanying functions may change from that schema into another (ie. from 'superlib' to 'restorelib') and the whole code is currently hardcoded to run with the named schema.
What I want is to be able to do one of the two: either pass the name of the schema where everything is located via a parameter to the stored procedure, or have the stored procedure detect the name of the schema and use it to run itself.
This is a sample of my current code:
SELECT COUNT(*) INTO TotalNotDone FROM superlib.PROCESSTABLES WHERE PROCESS_FLAG < 1;
WHILE TotalNotDone > 0 DO
SET SQLLOOPSTMT = 'select name_to_proces from ' CONCAT SOURCELIBRARY CONCAT '.processtables where process_flag = 0' ;
PREPARE LOOPSTMT FROM SQLLOOPSTMT ;
OPEN LOOPCUR ;
FETCH LOOPCUR INTO TABLETOPROCESS ;
CALL superlib.SP_RESTORE_INSERTS ( SOURCELIBRARY , DESTLIBRARY , TABLETOPROCESS, P_STARTTIME ) ;
CLOSE LOOPCUR;
SELECT COUNT(*) INTO TotalNotDone FROM superlib.PROCESSTABLES WHERE PROCESS_FLAG < 1;
END WHILE ;
What I want is for NOT to have to write superlib inside the stored procedure to call or reference the tables i'm using and just have the Stored Procedure recognize it currently IS living in the schema superlib.
I've tried SET CURRENT SCHEMA = 'SUPERLIB'; and SET SCHEMA = 'SUPERLIB'; but neither works when calling the TABLES.
I've also changed the path when creating the Stored Procedure from:
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","PROGUSER1" ;
to
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","SUPERLIB" ;
but that apparently does nothing.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我相信您需要在调用存储过程的连接上设置当前路径，而不仅仅是在创建它时。
请参阅：Weblogic：调用不带架构名称的 DB2 存储过程（属性 currentSchema）
当前路径文档在这里：http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0005877.htmi believe you'll need to set the
current pathon the connection that calls the stored proc, not just when creating it.see this: Weblogic: Call DB2 stored procedure without schema name (property currentSchema)
current pathdocumentation here: http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0005877.htm