A Scripts for Creating and Maintaining Oracle Database Provider for DRDA
Oracle Database Provider for DRDA needs several scripts to establish a proper environment.
catdrdaas.sql
The script catdrdaas.sql
creates Oracle Database Provider for DRDA catalog objects.
Rem catdrdaas.sql Rem Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem catdrdaas.sql - CATalog Oracle Database Provider for DRDA Rem Rem ===================================================================== Rem Exit immediately if there are errors in the initial checks Rem ===================================================================== WHENEVER SQLERROR EXIT; DOC ####################################################################### Customer should create the SYSIBM tablespace Eg: create tablespace SYSIBM datafile 'sysibm01.dbf' size 70M reuse extent management local segment space management auto online; ####################################################################### # @@prvtdpsadrda.plb
catnodrdaas.sql
The script catnodrdaas.sql
removes Oracle Database Provider for DRDA catalog objects
Rem catnodrdaas.sql Rem Rem Copyright (c) 2011, 2013, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem catnodrdaas.sql - CATalog NO Oracle Database Provider for DRDA Rem drop public synonym DBMS_DRDAAS; drop public synonym DBMS_DRDAAS_ADMIN; drop public synonym USER_DRDAASTRACE; drop public synonym DBA_DRDAASTRACE; drop public synonym ALL_DRDAASPACKAGE; drop public synonym USER_DRDAASPACKAGE; drop public synonym DBA_DRDAASPACKAGE; drop public synonym USER_DRDAASPACKSTMT; drop public synonym DBA_DRDAASPACKSTMT; drop public synonym ALL_DRDAASPACKAUTH; drop public synonym USER_DRDAASPACKAUTH; drop public synonym DBA_DRDAASPACKAUTH; drop public synonym ALL_DRDAASPACKSIDE; drop public synonym USER_DRDAASPACKSIDE; drop public synonym DBA_DRDAASPACKSIDE; drop role DRDAAS_USER_ROLE; drop role DRDAAS_ADMIN_ROLE; drop user SYSIBM cascade; commit; DOC ####################################################################### Customer should drop the SYSIBM tablespace. Eg: drop tablespace SYSIBM; #######################################################################
drdapkg_db2.sql
Rem drdapkg_db2.sql Rem Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem drdapkg_db2.sql - Initialize DRDA-AS environment so that Rem packages can be bound and correct datatypes Rem are returned for various SQL constructs Rem Rem DESCRIPTION Rem The DRDA-AS environment needs to be initialized so that the initial Rem packages (usually with an RDBCOLID of NULLID) can be bound. Rem Using the DataDirect driver, those package names look like Rem NULLID.DDOC510A, NULLID.DDOC510B, and NULLID.DDOC510C Rem Using the IBM driver (libdb2.so), the package names look like Rem NULLID.SYSTAT and NULLID.SYSshyxx (where s is one of L or S, Rem h is one of H or N, y is 1, 2, 3, or 4 and Rem xx is somwhere in 00 through FF) Rem Also, various columns must be TYPEMAPped -- their normal Rem attributes must be altered. Rem Rem The initial package bindings should be done under the id that runs Rem this script. That is, if we run this script under the Oracle ID Rem of xxxx, then the initial connection through an ODBC driver should be Rem using that same id, namely xxxx. Rem Rem NOTES Rem The following is relevant when using the IBM driver: libdb2.so ... Rem Note that the normal set of packages produced by the jdbcbinder Rem process (db2jdbcbinder in DB2/LUW) defines packages with names like Rem SYSSTAT and SYSLNmnn and SYSLHmnn. Thus, before running the Rem jdbcbinder on DB2/LUW specifying the Oracle Id accepted in the prompt Rem for this script, one needs to inform DRDA-AS that the id has the Rem required privilege, namely to create ANY package in the NULLID Rem schema. That is part of what we are doing here. Rem Rem This script can be run ONLY by a user that has the ability to use Rem the DRDAAS_ADMIN_ROLE which must be GRANTed to the user; also this Rem role must be active either by being set as a default ROLE or Rem by actively doing a SET ROLE DRDAAS_ADMIN_ROLE. SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET SERVEROUTPUT ON SHOW USER /* The following will work even if DRDAAS_ADMIN_ROLE is not one of the */ /* DEFAULT Roles, but HAS been GRANTed to the user running this script. */ /* (A prerequisite of setting a DEFAULT ROLE for a user is that the user*/ /* has been GRANTed that ROLE). */ SET ROLE DRDAAS_ADMIN_ROLE; Prompt Enter the OracleID under which the initial package BINDs will be made Prompt Use quotes (') if needed. Accept OracleID Define BindID = &OracleID Prompt Enter default collection ID for package binding (usually NULLID) Prompt Use quotes (') if needed. Accept DefaultCollection Define DfltCollid = &DefaultCollection declare id_passed CONSTANT VARCHAR2(128) := '&&BindId'; collid_passed CONSTANT VARCHAR2(128) := '&&DfltCollid'; id_to_use VARCHAR2(128); collid_to_use VARCHAR2(128); id_len PLS_INTEGER; collid_len PLS_INTEGER; quote CONSTANT CHAR := ''''; begin id_len := LENGTH(id_passed); collid_len := LENGTH(collid_passed); IF SUBSTR(id_passed, 1, 1) = quote AND SUBSTR(id_passed, id_len, 1) = quote THEN /* Use Id exactly as passed */ id_to_use := SUBSTR(id_passed, 2, id_len - 2); ELSE id_to_use := UPPER(id_passed) ; END IF; IF SUBSTR(collid_passed, 1, 1) = quote AND SUBSTR(collid_passed, collid_len, 1) = quote THEN /* Use Collection ID exactly as passed */ collid_to_use := SUBSTR(collid_passed, 2, collid_len-2) ; ELSE collid_to_use := UPPER(collid_passed) ; END IF; -- The following section is pertinent to ALL flavors of DB2 -- ========================================================= -- The id of the specified user will have ALL privileges for ANY Package in -- RDBCOLID=collid_to_use DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE( DBMS_DRDAAS_ADMIN.ALL_PRIVILEGE, collid_to_use,'*', id_to_use); commit; -- If you might want the id specified to create packages in rcbcolid= SCOTT, -- then you need to do the following: -- DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE(DBMS_DRDAAS_ADMIN.ALL_PRIVILEGE, -- 'SCOTT','*', id_to_use); -- commit; -- Typemaps ... -- The described "type" for "COUNT(*)" columns in any package in the -- collid_to_use schema should be INTEGER -- General "COUNT(*)" case DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'*','COUNT(*)', 'NUMBER=INTEGER'); -- ================================================== -- The following section is pertinent ONLY to DB2/zOS -- ================================================== -- To use DB2 z/OS SPUFI asgainst DRDAAS, the given oracle-id must be able to -- define packages in the DSNESPCS and DSNESPRR schemas -- -- DB2 z/OS SPUFI Packages DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE( DBMS_DRDAAS_ADMIN.ALL_PRIVILEGE, 'DSNESPCS','*', id_to_use); DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE( DBMS_DRDAAS_ADMIN.ALL_PRIVILEGE, 'DSNESPRR','*', id_to_use); commit; -- ====================================================================== -- Specific DataDirect ODBC package discovery queries for DB2 z/OS -- When accessing the collid_to_use.DDOC510A package, the -- "column" MAX(SECTNO) referencing table SYSIBM.SYSPACKSTMT (which is a -- NUMBER in Oracle terms) should be described as a SMALLINT to the -- application DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDOC510A', 'SYSIBM.SYSPACKSTMT:MAX(SECTNO)', 'NUMBER=SMALL'); -- Same as above but for package collid_to_use.DDOC510B DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDOC510B', 'SYSIBM.SYSPACKSTMT:MAX(SECTNO)', 'NUMBER=SMALL'); -- When accessing the collid_to_use.DDOC510A package, the -- "column" COUNT(DISTINCT(NAME)) referencing table SYSIBM.SYSPACKSTMT -- (which is a NUMBER in Oracle terms) should be described as a SMALLINT to -- the application DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDOC510A', 'SYSIBM.SYSPACKSTMT:COUNT(DISTINCT(NAME))', 'NUMBER=INTEGER'); -- Same as above but for package collid_to_use.DDOC510B DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDOC510B', 'SYSIBM.SYSPACKSTMT:COUNT(DISTINCT(NAME))', 'NUMBER=INTEGER'); commit; -- ====================================================================== -- Specific DataDirect JDBC package discovery queries for DB2 z/OS -- When accessing the collid_to_use.DDJC360B package, the "column" -- COUNT(*)-1 referencing table SYSIBM.SYSPACKSTMT (which is a NUMBER in -- Oracle terms) should be described as an INTEGER to the application DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDJC360B', 'SYSIBM.SYSPACKSTMT:COUNT(*)-1', 'NUMBER=INTEGER'); -- When accessing the collid_to_use.DDJC360B package, the "column" -- COUNT(*)-1 referencing table SYSIBM.SYSPACKSTMT (which might be described -- as NUMBER(0,-127) in Oracle terms) describe the column as an INTEGER -- to the application. DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDJC360B', 'SYSIBM.SYSPACKSTMT:COUNT(*)-1', 'NUMBER(0,-127)=INTEGER'); commit; -- ================================================== -- the following section is pertinent ONLY to DB2/luw -- ================================================== -- ====================================================================== -- Specific DataDirect ODBC package discovery queries for DB2/LUW DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDOC510A', 'SYSIBM.SYSPLAN:MIN(TOTALSECT)', 'NUMBER=SMALL'); DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDOC510A', 'SYSIBM.SYSPLAN:COUNT(*)', 'NUMBER=INTEGER'); -- DBMS_DRDAAS_ADMIN.SET_TYPEMAP(collid_to_use,'DDOC510A', -- 'MIN(TOTALSECT)', 'NUMBER=SMALL'); commit; -- ================================================== -- The following section is pertinent ONLY to DB2/iOS -- ================================================== -- Currently empty! end; /
drdasqtt_translator_setup.sql
Rem drdasqtt_translator_setup.sql Rem Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem drdasqtt_translator_setup.sql - Generalized script for setting up an Rem external SQL translator Rem Rem Rem DESCRIPTION Rem This script can be used to set up any external SQL translator. Rem Some translators, e.g., BableFish, may need extra customizations. Rem For BabelFish, that would include the source/target SQL text for Rem the fingerprint translations (to be inserted into Rem DBA_SQL_TRANSLATIONS). Rem Rem NOTES Rem Should be run "/ as sysdba" Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET SERVEROUTPUT ON show user Prompt Enter schema in which the SQL Translator Interface Package will be created as well Prompt as into which the third-party SQL translator will be loaded (usually SYSIBM). Accept TRANS_PKG_SCHEMA_ Prompt 'SQL Translator Interface Package Schema:' DEFINE TRANSLATOR_PACKAGE_SCHEMA = &TRANS_PKG_SCHEMA_ Prompt Enter unqualified name of the SQL Translator Interface Package Accept TRANS_PKG_NAME_ Prompt 'SQL Translator Interface Package Name:' DEFINE TRANSLATOR_PACKAGE_NAME = &TRANS_PKG_NAME_ Prompt Enter schema in which the Translation Profile will be created: Accept TRANS_PROFILE_SCHEMA_ Prompt 'Translation Profile Schema:' DEFINE TRANS_PROFILE_SCHEMA = &TRANS_PROFILE_SCHEMA_ Prompt Enter the unqualified name of the translation profile: Accept TRANS_PROFILE_NAME_ Prompt 'Translation Profile Name:' DEFINE TRANS_PROFILE_NAME = &TRANS_PROFILE_NAME_ Prompt Enter the "language" type of the translator: C, java, etc Accept TRANS_LANG_ Prompt 'Translator Language:' DEFINE TRANS_LANG = &TRANS_LANG_ Prompt Enter the names of the third-party SQL Translator objects; Prompt They should be available through rdbms/drdaas/jlib/.. Prompt If there is more than one object, enclose the entire set Prompt in four quotes, such as ''''object_a object_b''''. Accept EXTERNAL_CODE_ Prompt 'SQL Translator object(s):' DEFINE EXTERNAL_CODE = '''&EXTERNAL_CODE_''' DEFINE EXTERNAL_CODE Prompt Enter the signature for the entry for 'translateSQL' in one of the Prompt previously entered SQL Translator objects: Accept CALLOUT_TRANSLATE_SQL_ Prompt 'Entry for translateSQL:' DEFINE CALLOUT_TRANSLATE_SQL = '''&CALLOUT_TRANSLATE_SQL_''' Prompt Enter the signature for the entry for 'translateError' in one of the Prompt previously entered SQL Translator objects Accept CALLOUT_TRANSLATE_ERROR_ Prompt 'Callout for translateError:' DEFINE CALLOUT_TRANSLATE_ERROR = '''&CALLOUT_TRANSLATE_ERROR_''' Rem Create the SQL Translator Interface Package ... create or replace package &&TRANSLATOR_PACKAGE_SCHEMA..&&TRANSLATOR_PACKAGE_NAME as procedure translate_sql(sql_text in clob, translated_text out clob); procedure translate_error(error_code in binary_integer, translated_code out binary_integer, translated_sqlstate out varchar2); end; / show errors declare COMP_ERROR exception; pragma EXCEPTION_INIT(COMP_ERROR, -24344); translateSQLcode CONSTANT VARCHAR2(1024) := &&CALLOUT_TRANSLATE_SQL; translateErrorcode CONSTANT VARCHAR2(1024) := &&CALLOUT_TRANSLATE_ERROR; translateSQLToUse VARCHAR2(1024); translateErrorToUse VARCHAR2(1024); ln NUMBER; quote CONSTANT CHAR := ''''; my_cursor BINARY_INTEGER; n BINARY_INTEGER; i BINARY_INTEGER; package_body VARCHAR2(1024); /* we can't use bind variables to substitute for the "name" part of the */ /* procedures in the package body declaration; the "name" part MUST be a*/ /* single-quoted string!!! ARGHHH !!! */ package_body_1 VARCHAR2(400) := 'create or replace package ' || 'body &&TRANSLATOR_PACKAGE_SCHEMA..&&TRANSLATOR_PACKAGE_NAME as ' || 'procedure translate_sql(sql_text in clob, ' || ' translated_text out clob) as ' || 'language &&TRANS_LANG ' || 'name '''; package_body_2 VARCHAR2(400) := ''' ;' || 'procedure translate_error(error_code in binary_integer, ' || ' translated_code out binary_integer,' || ' translated_sqlstate out varchar2) as ' || 'language &&TRANS_LANG ' || 'name '''; package_body_end VARCHAR2(10) := '''; end;'; begin ln := LENGTH(translateSQLcode); IF SUBSTR(translateSQLCode,1,1) = quote AND SUBSTR(translateSQLCode,ln,1) = quote THEN translateSQLToUse := SUBSTR(translateSQLCode, 2, ln-2); ELSE translateSQLToUse := translateSQLCode; END IF; ln := LENGTH(translateErrorcode); IF SUBSTR(translateErrorCode,1,1) = quote AND SUBSTR(translateErrorCode,ln,1) = quote THEN translateErrorToUse := SUBSTR(translateErrorCode, 2, ln-2); ELSE translateErrorToUse := translateErrorCode; END IF; my_cursor := DBMS_SQL.OPEN_CURSOR; package_body := package_body_1 || translateSQLToUse || package_body_2 || translateErrorToUse || package_body_end; BEGIN DBMS_SQL.PARSE(my_cursor, package_body, DBMS_SQL.NATIVE); EXCEPTION when COMP_ERROR THEN DBMS_OUTPUT.PUT_LINE('SQLCODE=' || SQLCODE || ':' || SQLERRM); END; n := DBMS_SQL.EXECUTE(my_cursor); DBMS_SQL.CLOSE_CURSOR(my_cursor); end; / show errors Rem Load the Java code Rem CALL DBMS_JAVA.LOADJAVA('-definer -genmissing -schema SYSIBM Rem rdbms/drdaas/jlib/DBTooSQLAPI.jar rdbms/drdaas/jlib/DBTooTranslator.class', Rem '((* SYSIBM)(* PUBLIC)(* -))'); Rem DBTooSQLAPI.jar and DBTooSQLTranslator.class are fictional names set serveroutput on show user Rem Load the .class and .jar objects as specified ... declare extcode VARCHAR2(4096) := &&EXTERNAL_CODE; real_extcode VARCHAR2(4096); first_parm_first_part VARCHAR2(128) := '-definer -genmissing -schema &&TRANSLATOR_PACKAGE_SCHEMA '; first_parm VARCHAR2(4096); ln NUMBER; begin ln := LENGTH(extcode); /* We might have a beginning and ending aprostrophe --*/ /* we need to delete them */ IF SUBSTR(extcode,1,1) = '''' AND SUBSTR(extcode,ln,1) = '''' THEN real_extcode := SUBSTR(extcode, 2, ln-2); ELSE real_extcode := extcode; END IF; first_parm := first_parm_first_part || ' ' || real_extcode; /*DBMS_OUTPUT.PUT_LINE('First parm ' || first_parm); */ DBMS_JAVA.LOADJAVA(first_parm , '((* &&TRANSLATOR_PACKAGE_SCHEMA)(* PUBLIC)(* -))'); end; / GRANT EXECUTE ON &&TRANSLATOR_PACKAGE_SCHEMA..&&TRANSLATOR_PACKAGE_NAME to PUBLIC; GRANT CREATE SQL TRANSLATION PROFILE TO &&TRANS_PROFILE_SCHEMA; GRANT TRANSLATE ANY SQL TO &&TRANS_PROFILE_SCHEMA WITH ADMIN OPTION; CALL DBMS_SQL_TRANSLATOR.DROP_PROFILE('&&TRANS_PROFILE_SCHEMA..&&TRANS_PROFILE_NAME'); CALL DBMS_SQL_TRANSLATOR.CREATE_PROFILE('&&TRANS_PROFILE_SCHEMA..&&TRANS_PROFILE_NAME'); begin dbms_sql_translator.set_attribute('&&TRANS_PROFILE_SCHEMA..&&TRANS_PROFILE_NAME', dbms_sql_translator.attr_translator, '&&TRANSLATOR_PACKAGE_SCHEMA..&&TRANSLATOR_PACKAGE_NAME'); dbms_sql_translator.set_attribute('&&TRANS_PROFILE_SCHEMA..&&TRANS_PROFILE_NAME', dbms_sql_translator.attr_translate_new_sql, dbms_sql_translator.attr_value_true); end; / GRANT ALL ON SQL TRANSLATION PROFILE &&TRANS_PROFILE_SCHEMA.. &&TRANS_PROFILE_NAME TO &&TRANSLATOR_PACKAGE_SCHEMA ; GRANT USE ON SQL TRANSLATION PROFILE &&TRANS_PROFILE_SCHEMA.. &&TRANS_PROFILE_NAME TO DRDAAS_USER_ROLE;
drdasqt_set_profile_dd.sql
Rem drdasqt_set_profile_dd.sql Rem Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem drdasqt_set_profile_dd.sql - Set a sqllangprofile for each of Rem the DataDirect (dd) packages. Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 Rem You will be prompted for the profile name. Rem Must be run under an id that has access to the DRDAAS_ADMIN_ROLE role. Rem set echo on set serveroutput on SET ROLE DRDAAS_ADMIN_ROLE; prompt Enter the (qualified) profile name to use for DataDirect packages Accept SQLPROFILENAME Define PROFILE_NAMEX = &SQLPROFILENAME prompt Enter the default Package Collection (usually NULLID) Prompt Use quotes (') if needed Accept DefaultCollection Define PACKAGE_COLLECTIONX = &DefaultCollection declare TYPE FIRST_CHAR IS VARRAY(4) of CHAR(1); TYPE SECOND_CHAR IS VARRAY(3) of CHAR(1); first_chr FIRST_CHAR := FIRST_CHAR(); second_chr SECOND_CHAR := SECOND_CHAR(); package_name VARCHAR2(128); profile_name CONSTANT VARCHAR2(128) := '&&PROFILE_NAMEX'; package_collection_as_passed CONSTANT VARCHAR2(128) := '&&PACKAGE_COLLECTIONX'; package_collection VARCHAR2(128); cmd VARCHAR2(255); quote CONSTANT CHAR := ''''; ln BINARY_INTEGER; begin ln := LENGTH(package_collection_as_passed); IF SUBSTR(package_collection_as_passed, 1, 1) = quote AND SUBSTR(package_collection_as_passed, ln, 1) = quote THEN /* Use package_collection exactly as passed */ package_collection := SUBSTR(package_collection_as_passed, 2, ln - 2); ELSE package_collection := UPPER(package_collection_as_passed) ; END IF; first_chr.EXTEND(4); first_chr(1) := 'C'; first_chr(2) := 'S'; first_chr(3) := 'U'; first_chr(4) := 'R'; second_chr.EXTEND(3); second_chr(1) := 'A'; second_chr(2) := 'B'; second_chr(3) := 'C'; FOR f IN 1..first_chr.COUNT LOOP FOR s IN 1..second_chr.COUNT LOOP package_name := 'DDO' || first_chr(f) || '510' || second_chr(s); cmd := 'DBMS_DRDAAS_ADMIN.SET_PROFILE(' || package_collection || ',' || package_name || ',' || profile_name || ')'; DBMS_OUTPUT.PUT_LINE('Doing ' || cmd); DBMS_DRDAAS_ADMIN.SET_PROFILE(package_collection, package_name, profile_name); END LOOP; END LOOP; end; /