Citation: Eaton, R. (1992). Synonyms: The appendix of SQL/DS?  DATABASE Programming & Design, 5(10) 25-27As I lay in my hospital room recovering from an emergency appendectomy, the surgeon described the operation. His confidence was comforting; that is, until he tried to explain the purpose of the tissue he had removed from me so hastily. He couldn't find the right words. And as he attempted to convince me that he really did know about appendixes, I concluded that he really didn't know their purpose. His interest in appendixes surfaced only when they became infected

Eaton, Roger G.

K-State Research Exchange

SHOP' LK Synonyms: The appendix of SQL/DS? S I LAY IN MY HOS-pi tal room recover-ing from an emergen-cy appendectomy, the surgeon described the operation. His confidence was com-forting; that is, until he tried to ex-plain the purpose of the tissue he had removed from me so hastily. He couldn't find the right words. And as he attempted to convince me that he really did know about appendixes, I concluded that he really didn't know their purpose. His interest in appendixes surfaced only when they became infected. My initial impression of SQL/ DS synonyms was similar to the surgeon's impression of the appen-dix. I wasn't sure what value they had, but as long as they did not cause a database crash, I didn't really bother to find out ... that is, until the introduction of such data-base administration annoyances as: o Naming conventions that are management-mandated o Dynamic SQL embedded in COBOL o Test-to-production promo-tion errors o Security audits. I had to seek new and cre-ative solutions to these problems: I couldn't find relief in views, in-dexes, or even in views of views. I had to look elsewhere. My pro-posed solution was a liberal dose of synonyms and an additional standard or two, which could re-lieve the harsh sting of these DBMS irritants. In the SQL/DS universe, syn-onyms are substitute names for ta-bles and views. Naming require-ments are the same as table names. Usage, however, is optional. Each synonym is SQL/DS user specific. Thus, a table may support zero, one, or multiple synonyms. Since synonyms are user spe-cific (rather than global), each USER-BY R. G. EATON Curing the Common Database ID can have a synonym for any number of tables. Each synonym has its own life cycle-each remains active until explicitly dropped. A synonym is dropped when its as-sociated table or view is dropped. Figures 1 and 2 present two types of synonym usage. In Figure I, users would re-ceive the same results for each re-spective query: Acctng: SELECT" FROM BILL_ADDRESS Mrktng: SELECT" FROM PROSPECT _ ADDRESS Cstmrs: SELECT" FROM CUSTOMER_ ADDRESS However, as illustrated in Figure 2, users would receive dif-ferent results for each respective query: Acctng: saECT" FROM BILL_ADDRESS Payable: SELECT" FROM BILL_ADDRESS The DBMS maintains syn-onym information in its system ca-talogs-specifically, the SYSSYN-ONYMS catalog. If the user hasn't referenced the table or view (there-by creating "unqualified" table or view names), the preprocessor will simply assume that their names are synonyms. Many SQL/DS shops first used synonyms to abbreviate (or to make more memorable) table or view names, thus saving key strokes. The examples in Figures 1 and 2 repre-DATABASE PROGRAMMING & DESIGN 25 sent this usage. Synonyms also give a user-specific context to a table or view name. But let's consider wheth-er these benefits provide solutions to our burning DBMS issues: o Naming conventions that are management-mandated. Typi-cally, a simple installation includes a production and a test database. The test database reflects the actu-al production database structures and can use a subset of the data. In a hypothetical installation, the ap-plications managers have decided to use different names for produc-tion tables and views than those used for test. We won't address the justification for this requirement; it's a mandate-just do it. o Dynamic SQL embedded in COBOL. Preprocessing COBOL pro-grams containing dynamic SQL oc-curs at normal compile/preprocess and program run times. When a preprocessed program with dynam-ic SQL is executed, it ignores the USER-ID of the preprocessing user and uses the actual (run time) USER-ID to resolve security privi-leges. This approach is contrary to non dynamic, or static, execution. o Test-to-production promo-tion (jItrors. In our hypothetical in-stallation, the table names in the source code must be changed when migrating between test and pro-duction-which would expose the risk that test code could be cor-rupted after final testing and be-fore promoting to production. Ad-ditionally, changing production code to perform maintenance could be incorrectly changed before the maintenance modifications are even started. o Security audits. Static SQL COBOL programs resolve security privileges at preprocess time, not run time. Security is checked for the USER-ID preprocessing the pro-gram-not the actual user of the program. ···U~EfkID ACCTNG MHKTNG GSTMRS CUST~NAME-,ADDRESS CSTMHS CSTMRS.CLJST_ NAME_ADDRESS ACCTNG MAKTNG CSTMR$ SYNONYM BILL_APDRESS PROSPECT_ADDRESS CUSTOMER~ADDRESS FIGURE 1. A table supporting multiple synonyms. Dynamic SQL COBOL pro-grams resol ve securi ty pri vileges at run time. Each user must be granted appropriate privileges to execute and preprocess the pro-gram. A consistent method of con-trolling execution of both dynamic and static SQL is necessary. Addi-tionally, a method of restricting production promotion is a funda-mental requirement. With some in-genuity, synonyms can provide practical solutions. HE SIMPLEST WAY to explain how to im-plement synonyms is by using an outline to show the proce-dure. Each element of this imple-mentation progresses toward the next. These techniques will satisfy all described requirements with relative ease and minimal impact to performance. I. Naming conventions that are management-mandated. Develop test and production naming standards for the following: D USER-ID. Define a USER-Table Name: Table Creator's USER-IO: Fully Qualified Table Name: TablexName: Table Creator's USER~IO: Fully Qualified Table Name: U8ER-IO#1: .USER-ID #2: USER~JO ACCING PAYABLE ID for the preprocessor / compile execution. Synonyms will be de-fined for each USER-ID. (Examples are PRODI for the production en-vironment and TESTl for the test environment.) D CREATOR-ID. The CREA-TOR-ID will indicate the applica-tion. It should describe the appli-cation; for example, ACCTNG rep-resents an accounting application. Using a prefix to specify the envi-ronment satisfies the requirement of different test and production ta-ble names. The CREATOR-ID is used when creating tables for the application. (Two examples include PACCTNG for the production cre-ator, and TACCTNG for the test creator.) D Tables. The table name should be a descriptive abbrevia-tion of the table's purpose or type. D Views. The view name should be a descriptive abbrevia-tion of the view's purpose or type. D Synonyms. The synonym is a concatenation of the CREATOR-ID and the table or view name (the prefix is dropped). Because the po-CUST_NAME_ADDRESS CSTMRS CSTMRS.CUST _NAME_ADDRESS VENDOR_NAME_ADDR PAYABLE PAYABLE.VENDOR_NAME_ADDR ACOTNG CLERK SYNONYM BILL_ADDRESS BILL_ADDRESS. FIGURE 2. A synonym supporting multiple tables. OCTOBER 1992 26 Customer Name and Address Customer Service Department Accountant Marketing Staff Customer Service Staff TABLE NAME CSTMRS.CUST -,-NAME_ADDRESS CSTMRS.CUST _NAME_ADDRESS CSTMRS.CUST _NAME_ADDRESS tential length of the concatenated CREATOR-ID and table/view name exceeds the IS-byte maximum, the table/view name will be truncated accordingly. Synonyms are the same for both test and production USER-IDs. (PROD 1 creates the synonym CSTMRS_CUST _NAMLA for table PCSTMRS. CUST _NAME_ADDRESS, and TESTl cre-ates the synonym CSTMRS_CUST_ NAME_A for table TCSTMRS.CUST_ NAME_ADDRESS. II. Dynamic SQL embedded in COBOL. Include explicit CONNECT in-struction in all COBOL/SQL pro-grams. Standardize the USER-ID and PASSWORD host variable names; all programs must use the same variable name. Identify ta-bles by their PROD 1 and TESTl synonyms. III. Test-to-production promotion errors. Develop an interactive pre-process/compile procedure (such as a REXX EXEC) to: First, insert the USER-ID and PASSWORD into the source code; second, prepro-cess and compile the program; third, remove the USER-ID and PASSWORD from the source code; Customer Name and Address Customer Service Department Vendor Name and Address Accounts Payable Accountant Accounts Payable Staff TABLE NAME CSTMRS.CUST _NAME_ADDRESS PAYABLE.VENDOR_NAME_ADDR and fourth, grant RUN privileges to the appropriate users. The fourth step may be performed by a user with DBA authority rather than including it in the preprocess I compile exec. The authorized preprocessor I compile user would enter the USER-ID, PASSWORD, and list of the program's users. Use its syn-onym to reference a table or view. IV. Security audits. By imple-menting the interactive preprocess I compile procedure, all COBOL I SQL programs (including dynamic SQL) will be protected from un-authorized execution, thereby sat-isfying our security audit require-ments. Furthermore, the source code is relieved of the USER-ID and PASSWORD. At preprocess I compile time, the synonyms are resolved to the USER-ID that is furnished to the preprocessor I compile exec. The following three applica-tions and creator-IDs are included in Figure 3: Customer Service Accounting Marketing CST MRS ACCTNG MRKTNG Each application has one ta-ble and program. The correspond-ing test tables are: TCSTMRS.CUST _ NAME _ ADDRESS TMRKTNG.PURCHASE _ HISTORY TACCTNG.ACCOUNT _BALANCE All test table creators grant all privileges with grant option to the TESTl creator-ID. Note that test ta-bles are distinguished by the first character in the creator-ID. Each program contains stan-dard host variable names for the connect-ID and password. (The ac-tual connect-ID and password are stored within a protected table.) Table Iview names aren't qualified in the program; that is, the creator-ID isn't included. REXX EXEC inserts the actual connect-ID and password into the COBOL source code just before the preprocessor and compiler are ex-ecuted. After the preprocessor and compiler complete, the REXX EXEC removes the connect-ID and pass-word. The preprocessing is per-formed with PROD! as the crea-tor I connect-ID. Developers can preprocess and compile under their own crea-tor I connect-ID. Therefore, if they require changes to an existing ta-ble, they can create the table under their creator-ID without changing the table name in the program. Meanwhile, the altered table can coexist with the test and produc-tion tables. ALTHOUGH THEIR value wasn't initially obvious, I am now convinced that syn-onyms can play an important role in database imple-mentations. They are much more significant than the novel ty they were once considered. As discussed earlier, synonyms are a vital data-PCSTMRS creates CUST_NAME_AOORESS table and grants ill privileges to PROOl with GRANT OPTION PMRKTNG creates PURCHASCHISTORY table and grants ALL privileges to PROOl with GRANT OPTION PACCTNG creates ACCOUNT_BALANCE table and grants ill privileges to PROOl with GRANT OPTION PCSTMRS creales a program that uses: CSTMRS_CUST_NAME_A and ACCTNG_ACCOUNT_BAL PMRKTNG creales a program that uses: CSTMRS_CUST_NAME_A and MARKETlNG_PURCHASE_HI PACCTNG creates a program that uses: MRKTNG]URCHASE_HI and ACCTNG_ACCOUNT_BAL base component. While I hope this approach clarifies the value of SQL/DS synonyms, medical science will have to continue their search to determine a use for the human appendix. 1111 Editor's note: The SQU DS environment is the lead feature ill this month's Product Watch. See page 75. REFERENCES IBM. "SQL/DS Concepts and Facilities," 1984, IBM order number, GH24-5013. IBM. "SQL/DS Database Planning and Administration," 1987, IBM order number, SH09-8024. IBM. "SQL/DS Terminal User's Refer-ence," IBM order number, SH24-5017. R. G. Eaton is a development team leader at Twentieth Century Services Inc. in Kansas City, Missouri. Preprocessing/compiling the test versions of these programs would require the same processing as the PROOl versions. In PROOl grants RUN for each program to appropriate users other words, replace the: PROOl with TESTl PCSTMAS with TCSTMRS PMRKTNG with TMRKTNG PACCTNG with TACCTNG FIGURE 3. Using synonyms to streamline test-to-production turnover. DATABASE PROGRAMMING & DESIGN 27 

Synonyms: The appendix of SQL/DS?

http://krex.k-state.edu/dspace/bitstream/2097/32826/1/Oct%2092%2025-27.pdf

Synonyms: The appendix of SQL/DS?

Abstract

Similar works

Full text

Available Versions

K-State Research Exchange