DB2 packages: Concepts, examples, and common problems
http://www-128।ibm.com/developerworks/db2/library/techarticle/dm-0606chun/
John Chun (chunj@ca.ibm.com), DB2 Advanced Support Specialist, IBM
Paolo Cirone (pcirone@ca.ibm.com), DB2 Advanced Support कोन्सुल्तंत
今天在华为做事的时候说起package，想起了这篇文章中的内容。回来再看看，顺便就贴在这里：）以后自己查起来也方便，哈哈。偶基本上本着方便自己然后方便大家的原则，莫拍！哈哈。
2 comments:
One important table is the SYSCAT.PROCEDURES table. If you query this table after creating the PAOLO.RAISE procedure, you will see the following entry for the procedure:
Listing 23. SYSCAT.PROCEDURES output
PROCSCHEMA PROCNAME SPECIFICNAME PROCEDURE_ID CREATE_TIME
------------- ----------- --------------------- ------------ ----------------------------
PAOLO RAISE SQL060423221235152 65752 2006-04-23-22.12.35.221806
Notice the SPECIFICNAME column entry. This will come in handy when you look at SYSCAT.ROUTINEDEP. This table holds routine dependencies, including a column called BNAME. The value for BNAME for the procedure will be important when you look at the next table. Notice that we matched the SPECIFICNAME from the first table with the ROUTINENAME of the second table (SYSCAT.ROUTINEDEP).
Note: SYSCAT.ROUTINEDEP supercedes SYSCAT.FUNCDEP in V810 and beyond. However, FUNCDEP will still remain as it was in V710.
Listing 24. SYSCAT.ROUTINEDEP output
PROCSCHEMA ROUTINENAME BTYPE BSCHEMA BNAME
------------- --------------------- ----- ------- ------------
PAOLO SQL060423221235152 K PAOLO P2123518
The next step is to go to the SYSCAT.PACKAGES table and use the info on hand to get the final piece of the puzzle.
Listing 25. SYSCAT.PACKAGES output
PKGSCHEMA PKGNAME BOUNDBY DEFINER DEFAULT_SCHEMA VALID UNIQUE_ID
------------ ---------- --------- ------- -------------- ----- ---------
PAOLO P2123518 PCIRONE PCIRONE PCIRONE Y XAjMWXEW
TOTAL_SECT FORMAT ISOLATION BLOCKING INSERT_BUF
---------- ------ --------- -------- ----------
1 0 CS U N
So take BNAME, go to the SYSCAT.PACKAGES table, and see that it corresponds to the value in the PKGNAME column. BNAME in the previous table is the actual package name for this stored procedure. You will see other information on this package, as well, here. Things like the UNIQUE_ID (consistency token), isolation level for the package and SQL, and so on.
Using this information, it is simple to find the package from the function name and find the function name from the package name. This should help in various troubleshooting situations when trying to pull all the pieces together with routines and packages.
An additional procedure is made available to customers if they wish to rebind an SQL procedure's package without having to know the package name itself. REBIND_ROUTINE_PACKAGE is a stored procedure that will take three parameters, one of which is the procedure name you wish to rebind. Check the Information Center for more details on this method to rebind your procedures.
发表评论 Add/View Comments