Home

Oracle Procedural Gateway Visual Workbench For WebSphere MQ

1. sssssssseseseee ene een enne 7 1 WMhats in a MIP2 4 neenon Ie amari eie ied d Ue d t de Ee eds 7 2 QOPEN and OCLOSE ws dientes deae meu 7 2 ENOUEUE and DEGQUEUE 2 n niter tete eee Petre tede pe HE ee ee dat 7 8 MIP Specification Example etre e ne irte tree eer Per ER read 7 8 MIP Procedutes ue RR Ree telis ee ee EE UR E I E 7 6 OOPEN Procedure eese ema Danae esee teda e e P ed eee eter iet 7 7 OOPEN Syntax tetesibgentepa eene ter He ibit rdi terne bnt eere Pos 7 7 OCLOSE Proceduxe non gie qi E eO RR RR REPRE ERO ROT deb 7 7 OCLOSE Syritaxi ath adu RD DD ote Lane i btt E ath cto Ite aurea 7 7 ENQUEUE Procedute e uet e e eite eio eei dei t epe e HRS ee EG 7 7 ENQUEUE Naming and Name Resolution sse eee nens 7 8 Basic ENQUEUE Procedure 3 e eee SE eet lt ded 7 8 N T a i D dM EHE EE 7 8 bcd 7 8 Advanced ENQUEUE Procedure sess eese eere enne rn etre retten ne nennen ener inn 7 9 DYLAN rss D T 7 9 Exam ple 7 10 DEQUEUE Procedure o soU eoe eee pet ei ROREM Miata astral va bte ie 7 11 DEQUEUE Naming and Name Resolution sss 7 11 Basic DEOUEUE Procedt re a ad eed uten dette ted 7 11 anc c 7 12 BExamplece ctt Streets du LU IM MINCE IPM iD DM S 7 12 Advanced DEQUEUE Procedure eese eene nenne trennen testi tenens nere nnenn 7 12 SVDt X Gctuufieunitbodeen epe rubei fed uie din tad trie Sh Pd tee a
2. Note If your production Oracle server is Oracle8i or earlier you need to use pgmdeploy8 sql to install the current release of PG64MQ Visual Workbench deployment packages and you need to use pgmundeploys sql to remove previous release of PG4MQ Visual Workbench deployment packages All of the examples in this section are provided with the assumption that you are installing on the current release Verifying and Installing PL SQL Packages 1 Locate the necessary scripts pgm sql pgmbqm sql pgmdeploy sql pgmsup sql pgmundeploy sql prvtpg sql a utlpg sql These scripts are installed with the gateway in the directory ORACLE _ HOME pg4mgq admin deploy where ORACLE HOME is the gateway home directory If your production Oracle server is on a different computer than the gateway you need to use a file transfer method such as ftp to transfer files in the directory ORACLE HOME pg4mgq admin deploy where ORACLE HOME is the gateway home directory on your gateway computer On your production Oracle server computer change directory to the directory containing the deployment scripts you just transferred and skip to step 4 If your production Oracle server is on the same computer as the gateway then change directory to ORACLE _HOME pg4mq admin deploy where ORACLE _ HOME is the gateway home directory Run the deployment script by entering sqlplus nolog pgmdeploy sql At the script prompt En
3. Caution Before using the Visual Workbench for development ensure that the repository exists Operating System Microsoft Windows 32 bit is required Oracle Procedural Gateway This release supports release 10 2 of the Oracle Procedural Gateway for WebSphere MQ For more information see the gateway installation guide for your platform Oracle Server An Oracle server is required to use the gateway and the PG4MQ Visual Workbench For more information refer to the gateway installation guide for your platform File Transfer PG4MOQ Visual Workbench requires a file transfer product such as FTP capable of moving files between systems 3 2 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide 4 Preinstallation This chapter guides you through the basic concepts and preinstallation steps for Oracle Procedural Gateway Visual Workbench for WebSphere MQ 10g release 2 10 2 The following topics provide information about Oracle Procedural Gateway Visual Workbench for WebSphere MQ environment variables settings and starting Oracle Universal Installer m Preinstallation Tasks a About Oracle Universal Installer Preinstallation Tasks Perform the following tasks before installing the current release of PG4MQ Visual Workbench 1 Start your operating system 2 Logonasa member of the Administrators group to the computer on which to install the current release of PG4MQ Visual
4. Figure 6 9 Dialog Box for MQSeries Properties Message Queue Definitions Revert Cancel Security ID aa Help Message Options Enqueue Option Response Queue ss Visibility On Commit Y Priority Default DIXI DIT Expiration seconds Never Wait Interval seconds _ Forever ij Acknowlegement Visibility oncommit Delivery Mode Not Persistent E DEUS MOS Message Queue Definitions Profile Name Queue Name Security ID Message Options Response Queue Unique name you specify for this message queue definition The Profile Name Must be unique Can contain alphanumeric characters plus the characters underscore _ dollar sign and number sign a s not case sensitive Name of the message queue where messages are to be sent or retrieved Queue Name is case sensitive You must enter the name in the correct case Queue Name can be up to 48 characters long The administrator who set up the message queuing system can supply this name Security identity associated with the application that is sending enqueuing the message The security identity is an optional password that might be used between applications If the sending application uses a security ID then the message queuing system forwards it from the sending application to the retrieving dequeuing application Security ID can be up to 12 characters long Name of the queue in which response messages are to be returned by the retrieving applicati
5. See Also Using the MIP Templates on page 6 28 and Chapter 5 Installation for more information If the compile fails then click Show Errors to see the list of compilation errors Figure 6 26 shows the Error Messages dialog box Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 25 Using the MIP Templates Figure 6 26 Dialog Box for Error Messages eg Error Messages X Compilation errors for package A123456789012345 une co we Error Me 8 46 specification PLS 00498 illegal use of a type before its dec 7 4 specification PL SQL Declaration ignored 11 54 specification PLS 00498 illegal use of a type before its dec 11 4 specification PL SQL Declaration ignored Click View PL SQL Code to see the PL SQL code location of the error Using the MIP Templates When you create a MIP the PG4MQ Visual Workbench generates a template for each ENQUEUE and DEQUEUE procedure specified in the MIP The templates demonstrate how the MIP is used in a PL SQL program and how it can be used to verify the generated MIP and the data conversion package See Also Chapter 7 Using the Generated MIP for more information To see the MIP templates 1 m B 9 Open the PG4MQ Visual Workbench repository Open the Interfaces folder Open the PL SQL folder Select the interface whose templates you want to see by clicking it once Select a template from the list in the PL SQL code
6. The MIP ENQUEUE procedure Using the Generated MIP 7 7 ENQUEUE Procedure Converts a message from PL SQL format to native format according to the specifications of a data profile Sends the converted message to a queue of a non Oracle message queuing system according to the specifications of a message queue profile or the input arguments of the procedure ENQUEUE Naming and Name Resolution Because the ENQUEUE procedures are part of a MIP applications must specify this fully qualified name when calling the procedure Schema name mip name ENQUEUE The following table describes the parameters that need to be specified Parameter Description schema name Name ofthe Oracle user that compiled the MIP mip name Name you specified when you created the MIP PL SQL runs the correct ENQUEUE procedure based on the parameters provided For example if only the payload output parameter is specified the basic ENQUEUE procedure is run When more than one data profile is added to a MIP and multiple versions of basic and advanced ENQUEUE procedures are generated for the MIP the type of payload parameter used determines which ENQUEUE procedure in the MIP is selected Basic ENQUEUE Procedure The basic ENQUEUE procedure converts the message content of payload according to the specifications of the data profile and sends the message to a queue according the specifications of the message queue profile If the basic ENQUEUE
7. See Also Creating a Message Queue Profile on page 6 9 for more information 4 Using the PG4MQ Visual Workbench Wizard define the interface profile by selecting one or more data profiles and one message queue profile to associate with the interface The PG4MQ Visual Workbench automatically generates the MIP See Also Creating an Interface Profile and Generating a MIP on page 6 14 for more information 5 Test the message queuing gateway associated with the MIP See Also Testing the Gateway on page 6 22 for more information 6 Compile the MIP in the PG4MQ Visual Workbench repository See Also Compiling the MIP on page 6 26 for more information 7 Complete the MIP procedures in the PG4MQ Visual Workbench repository See Also Using the MIP Templates on page 6 28 includes information about testing the MIP procedures 8 Compile the MIP on the production Oracle server and test it there See Also Preparing the MIP for Production on page 6 34 for more information 6 2 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Starting the PG4MQ Visual Workbench and Connecting to a Repository Starting the PG4MQ Visual Workbench and Connecting to a Repository Start the PG4MQ Visual Workbench by double clicking its icon in the Start menu list of programs in the Oracle for Microsoft Windows 32 bit folder The Conne
8. The following table describes the parameters that must be specified Parameter Description queue name Name of the queue to be opened If the value is NULL then the queue name from the message queue profile is used open options Defines the open options that the calling application can specify when calling QOPEN See Also PGM_BOM Package on page 7 17 for more information NULL is an invalid parameter queue handle Returns the handle specified by queue name QOPEN returns the handle to the queue to be opened If you call QOPEN multiple times in sequence then a new queue handle is returned each time and the previous handle becomes invalid QCLOSE Procedure QCLOSE relinquishes access to a queue object You must call QCLOSE after all messages have been processed using the advanced ENQUEUE and DEQUEUE procedures It is not necessary to perform a QCLOSE after processing messages using the basic ENQUEUE and DEQUEUE procedures The basic version ENQUEUE and DEQUEUE close the queue after sending and retrieving messages QCLOSE Syntax PROCEDURE QCLOSE queue handle IN OUT queue handle typ where queue handle specifies the handle of the queue being closed After QCLOSE is called the queue handle becomes invalid and cannot be used for subsequent ENQUEUE or DEQUEUE procedures If QCLOSE fails then it raises a PL SQL exception The application calling the procedure is responsible for handling the exception ENQUEUE Procedure
9. The presence of the editing symbols means that the remote host field contains the data content and length indicated The editing positions are included in the length of the data field but all field positions are converted as a single string and no special scanning or translation is done for edited byte positions Edited positions in data retrieved from the remote host are converted along with the entire field and passed to the client application in the corresponding PL SQL output variable defined as a VARCHAR2 data type For example PIC XXXBBXX Alphanumeric field 7 bytes long and is converted in a single conversion call No testing or translation is done on the contents of the byte positions indicated by B Although VS COBOL II language rules indicate that these positions contain blank in the character set specified for the remote host the data that is present is the user s responsibility m PIC GGBGGG Double byte character set field 12 bytes long and is converted in a single conversion call No testing or translation is done on the contents of the byte positions indicated by B Although VS COBOL II language rules indicate that these positions contain blank in the character set specified for the remote host the data that is present is the user s responsibility The Visual Workbench converts the COBOL 9 data type to a PL SQL NUMBER data type of the same precision and scale NLS character set translation is also performed on signs curre
10. Specify Database Link 2 Select Data Profiles 3 Select Message Queue profile Click Next to begin creating a new interface Click here to skip this message next time Cancel Help Back Next gt nis Click Next to begin the Wizard s step by step instructions to Name the interface profile which becomes the name of the generated MIP Specify a database link Add one data profile Add one message queue profile Click Finish after verifying your selections signaling the PG4MQ Visual Workbench to generate the MIP code If you need help using the Wizard then click Help Note The MIP generated with this release of the PG4MQ Visual Workbench is not compatible with the MIP released with the PG4MQ Visual Workbench release 9 2 0 x x and earlier You must modify applications that use the Beta version of the MIP The PG4MQ Visual Workbench repository is not compatible with the MIP released with the PG4MQ Visual Workbench release 4 0 1 1 1 and earlier You must re create interface profiles that were created and stored in the repository with the beta release Viewing the Generated Code After you create an interface profile with the Wizard the PG4MQ Visual Workbench generates The new MIP One data mapping package for each data profile in the MIP MIP templates that show you how to use the generated MIP the template code can be used as the starting point for development See Also Using the MIP
11. The description is forwarded by the message queuing system as is from the enqueuing application to the dequeuing application Applications are responsible for the usage of the specified response queue response queue Name of the response queue for the message The response queue name is forwarded by the message queuing system as is from the enqueuing application to the dequeuing application Applications are responsible for the usage of the specified response queue A response queue is required if acknowledgment is specified as either POSTTIVE or NEGATIVE delivery mode Specifies whether enqueued messages survives a system failure PERSISTENT specifies that messages survive a system failure NOT PERSISTENT specifies that messages do not survive a system failure acknowledgment Specifies whether the enqueing application receives an acknowledgment upon receipt of the message by the dequeuing application NONE specifies that no acknowledgment message is provided POSITIVE specifies that an acknowledgment message is provided both when a message is retrieved and when it is not retrieved NEGATIVE specifies that an acknowledgment message is provided only if the message is not retrieved The acknowledgment message is delivered to the queue specified by response queue security id Security identity associated with the application enqueuing the message The property is forwarded by the message queuing system as is from the enqueui
12. a The DEQUEUE test waits the specified amount of time and then reports the exception a If the Wait Interval is not set and no messages are on the queue then the DEQUEUE test waits forever the default See Also Creating a Message Queue Profile on page 6 9 for information about the Wait Interval Preparing the MIP for Production After completing the development phase deploy the MIP to one or more production Oracle servers where you plan to run your applications that use the MIP Follow the instructions in this chapter to prepare the MIP for use in a production system Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 31 Preparing the MIP for Production Before You Compile the MIP on the Production Oracle Server You must compile the MIP on the Oracle production server before your applications can use the MIP The following requirements must be met before you compile a MIP at the production Oracle server ASQL Net listener for the production Oracle server must be running Refer to your Oracle SOL Net documentation for information The tnsnames ora file on the production Oracle server must contain an entry specifying the procedural gateway Refer to the gateway installation guide for your platform for information a A database link to the procedural gateway must be created at the production Oracle server to match the name of the database link that was used when you defined the interface prof
13. unlimited time nnspecifies the number of seconds the message remains on the queue Depending on how the queues and the event handling of the message queuing system are configured the message queuing system might place expired messages on dedicated event queues For the WebSphere MQ product the minimum value is 1 and the maximum value is 231 1 Specifies whether the enqueuing application receives an acknowledgment when the dequeuing application retrieves a message a None specifies no acknowledgment message Positive specifies that an acknowledgment message is provided both when a message is retrieved and when it is not retrieved Negative specifies that an acknowledgment message is provided only if the message is not retrieved The Acknowledgment message is delivered to the queue specified by the Response Queue parameter Specifies whether messages are kept on the queue after a system failure a Not Persistent specifies that the message is removed from the queue after a system failure This is the default Persistent specifies that a message is kept on the queue after a system failure Specifies transaction behavior of the ENQUEUE requests On Commit specifies that the ENQUEUE is part of the current transaction The operation is completed when the transaction commits On Commit is the default Immediate specifies that the ENQUEUE is not part of the current transaction The operation constitutes a transac
14. viii for a list of books that will provide more information about this parameter Incorrect Precision of PL SQL Data Types The COBOL to PL SQL map panel occasionally lists an incorrect precision for the PL SQL data types shown To see the correct PL SQL precision view the generated PL SQL code of a MIP to which the COBOL data profile was added Select the data mapping entry from the list on the PL SQL Code panel for the MIP and click View The PL SQL package specification includes the data type definitions generated and shows the correct precision of each data type See Also Viewing the Generated Code on page 6 17 for more information The MIP Is Too Large When creating an interface profile or adding data profiles to an interface profile the MIP can become larger than the PG4MQ Visual Workbench can handle The PG4MQ Visual Workbench reports this error as follows 2 2 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Known Problems ORA 20004 Message Interface Package getting too large Release Information 2 3 Known Problems 2 4 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide 3 Requirements The Oracle Procedural Gateway Visual Workbench for WebSphere MQ is used with the Oracle Procedural Gateway for message queuing products This release supports release 10 2 of the Oracle Procedural Gateway for WebSphere M
15. 5 3 installing the repository 5 3 PG4MOQ Visual Workbench 1 6 private 5 7 remove 5 7 server definition 5 4 requirements hardware 3 1 software 3 1 Response Queue parameter for acknowledgment message 6 11 parameter for message queue profile 6 11 response_queue parameter 7 19 role PGMDEV 5 6 S sample copybook files 6 6 script file directory 5 4 pgmbqms8 sgl A 2 pgmdeploy9 sql A 1 pgmsup8 sql A 2 pgmundeploy9 sql A 1 pgvwbremove sql 5 7 pgvwbrepos sql 5 5 5 6 prvtpg sql A 2 prvtrawb plb 5 4 utlpg sql A 2 utlraw sgl 5 4 Security ID parameter for message queue profile 6 10 security id parameter 7 19 SID system identifier parameter 6 4 default 6 4 software requirements 3 1 starting the PG4MO Visual Workbench 6 3 Index 5 statement DESCRIBE 5 4 GRANT 6 35 SYNCHRONIZED 7 25 SYNCHRONIZED LEFT 7 25 SYNCHRONIZED RIGHT 7 25 system identifier SID parameter 6 4 I template adding code to 6 30 MIP 6 17 testing the DEQUEUE 6 33 testing the ENQUEUE 6 32 using the MIP 6 28 using the MIP introduction 1 5 using to test the MIP 6 31 template code 6 17 to test the MIP 6 26 test code package 6 36 testing introduction to testing the gateway for a MIP 1 5 testing the gateway for a MIP 6 22 tnsnames ora file 6 34 truncated and non unique names in data conversion 7 26 type definition anintroduction 7 14 DEQUEUE OPTIONS Typ 7 15 ENQUEUE OPTIONS Typ 7 15
16. 7 2 databaselink 7 2 definition 7 1 DEQUEUE procedure 7 11 ENQUEUE procedure 7 7 example 7 3 example DO DEPOSIT 6 17 6 18 for correct PL SQL precision 2 2 generated data mapping package 6 17 generated templates 6 17 generating 6 14 generating gateway test code 6 22 incompatible with beta version 2 1 6 17 introduction description 1 2 introduction to generating 1 4 message queue profile 7 2 migration considerations 2 1 overview of PG4MQ Visual Workbench development 6 2 parameter restrictions a table 7 19 preparing for production 1 6 6 34 procedures 7 6 testing the gateway for introduction to 1 5 toolarge 2 2 updating 6 19 using a template to test 6 31 using the templates 6 28 using the templates introduction 1 5 msgid parameter 7 9 7 12 7 19 N naming algorithm PL SQL 7 25 national language support parameter for data profile 6 6 NLS see national language support no messages available DEQUEUE application 6 34 NULL Index 3 DEQUEUE procedure action 7 12 ENQUEUE procedure action 7 9 example syntax 7 5 7 6 7 14 7 15 7 16 7 20 invalid for open mode 7 14 invalid for payload 7 8 7 10 7 12 QOPEN syntax 7 7 O OCCURS clause 7 24 open_options parameter 7 14 OPEN_OPTIONS_Typ type definition 7 14 openOpts open_mode parameter 7 10 ORA 01000 error message 2 2 ORA 20004 error message 2 2 Oracle Procedural Gateway for message queuing systems 1 1 Oracle SID
17. Installer To start the installer insert the PG4MQ Visual Workbench installation media into your computer s installation media drive and perform the following steps 1 Choose Start Run 2 Enter Drive setup exe in the Open field of the Run dialog box where Drive is the drive designation for the installation media drive that contains the PG644MQ Visual Workbench installation media For example if your installation media drive is D then you would enter d setup exe Click OK to start the installer Click Deinstall Products The Inventory dialog box appears Expand the tree of installed components until you find the components to remove Check the boxes of the components to remove Click Remove The Inventory Confirmation window appears o N DP oO FP s Click Yes to remove the selected components Note A message may be displayed indicating that removing some components may cause other components to not perform properly The components are removed from your computer The Inventory dialog box appears without the removed components 9 Click Close to close the Inventory dialog box 10 Click Exit to exit Oracle Universal Installer Visual Workbench Installation Complete When the installation is complete the PG4MQ Visual Workbench icon appears in the Oracle for Microsoft Windows 32 bit folder Figure 5 1 Visual Workbench Icon oracle PG4MQ Visual Workbench You can start the Visual Workbench a
18. RAW These PL SQL packages must be installed on the production Oracle server when data conversion packages are used in run time environments See Also Preparing the MIP for Production on page 6 34 and Appendix A Preparing the Production Oracle Server for more information You can view the data conversion package by using the Code Viewer Select the Cobol Mapping entry from the list and click View Code The Code Viewer appears See Also Viewing the Generated Code on page 6 17 Note Youcan view the body of the data conversion package Do not modify the package body or specification as it might cause the calling Oracle application to malfunction or lead to unrecoverable errors COBOL Data Type Conversion PIC X The Visual Workbench supports IBM VS COBOL II specified as IBMVSCOBOLII when defining data profiles Visual Workbench converts the COBOL x data type to a PL SQL CHAR data type of the same character length NLS character set conversion is also performed COBOL lacks a data type specifically designated for variable length data Such data is represented in COBOL as a subgroup containing a PIC 9 length field followed by a PIC X character field For example 10 NAME 15 LENGTH PIC S9 4 15 LETTERS PIC X 30 It cannot be guaranteed that all instances of an S9 4 field followed by an X field are always variable length data Instead of converting the COBOL group NAME to a PL SQL V
19. Select Test Operation C MIP ENQUEUE operation using template code C MIP DEQUEUE operation using template code C Gateway DEQUEUE operation Test results Run Test top Rur Edit Template Close Revert Help 7 Click Run Test The Message Interface Package Tester shows the result of the test The test fails if the database link is not set correctly or if the gateway is not functioning properly Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 21 Testing the Gateway In the example shown in Figure 6 22 the ENQUEUE operation successfully sent a message to the local queue Figure 6 22 Dialog Box for Message Interface Package Tester Message Interface Package Tester x 4 g Test Interface DO_DEPOSIT nt Connection Select Test Operation C MIP ENQUEUE operation using template code C MIP DEQUEUE operation using template code Gateway ENQUEUE operation C Gateway DEQUEUE operation Test results esting gateway ENQUEUE operation est ran successful Finished test run 8 Choose Gateway DEQUEUE Operation 9 Click Run Test The Message Interface Package Tester shows the result of the test In the example shown in Figure 6 23 the DEQUEUE operation successfully retrieved a test message from the local queue 6 22 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Testing the Gateway Figure 6 23 Dialog Box for Message Inte
20. Typ TYPE ZIP Typ02 is RECORD FIRST FIVE CHAR 5 LAST FOUR CHAR 4 TYPE OFFICE ADDRESS Typ is RECORD STREET CHAR 20 CITY CHAR 15 STATE CHAR 2 ZIP ZIP Typ02 TYPE EMPREC Typ is RECORD HIREDATE CHAR 8 BIRTHDATE CHAR 8 SKILL SKILL Tbl EMPNO NUMBER 4 0 EMPNAME EMPNAME Typ HOME ADDRESS HOME ADDRESS Typ DEPT CHAR 45 OFFICE ADDRESS OFFICE ADDRESS Typ JOBTITLE CHAR 20 In Example 7 3 note that the 02 appended to the second declaration ZIP Typ02 and its reference in OFFICE ADDRESS and the fully qualified reference to the PL SQL variable that corresponds to the following a HOME ADDRESS ZIP is packagename EMPREC Typ HOME ADDRESS ZIP LEFTMOST FOUR packagename EMPREC Typ HOME ADDRESS ZIP RIGHTMOST FIVE OFFICE ADDRESS ZIP is packagename EMPREC Typ OFFICE ADDRESS ZIP FIRST FIVE packagename EMPREC Typ OFFICE ADDRESS ZIP LAST FOUR 7 28 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide PL SQL Naming Algorithm The nested record type name ZIP Typ02 is not used in the reference but is implicit within PL SQL s association of the nested records Using the Generated MIP 7 29 PL SQL Naming Algorithm 7 30 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide A Preparing the Production Oracle Server Specific PL SOL packages must be present on your production Oracle server before y
21. WebSphere MQ Installation and User s Guide About the Message Interface Package MIP ENQUEUE and DEQUEUE The ENQUEUE procedure ends a message to the queue specified in the message queue profile See Also ENQUEUE Procedure on page 7 7 for more information The DEQUEUE procedure retrieves a message from the previously opened queue See Also DEQUEUE Procedure on page 7 11 for more information The MIP contains a basic and an advanced version of the ENQUEUE and DEQUEUE procedures for each data profile associated with the MIP a The basic version takes all options and properties from the message queue profile It requires one argument a message content buffer identified as payload in the syntax described in this chapter Theadvanced version has additional parameters that let the calling application control how to send or retrieve a message The calling application can override the options and properties specified in the message queue profile and can set other options Each data profile attached to the MIP has its own set of ENQUEUE and DEQUEUE procedures in the MIP The ENQUEUE procedure takes a message on input formatted according to the PL SQL type created for the data profile The DEQUEUE procedure delivers a message as output formatted according to the PL SQL type created for the data profile MIP Specification Example This example shows a MIP specification generated for the interface profil
22. Workbench Note Do not manually set ORACLE HOME in the environment path This is done automatically in the registry 3 Stop all Oracle services if any are running for the Oracle home into which you want to install PG4MQ Visual Workbench a Choose Start gt Settings gt Control Panel gt Services b If any Oracle services their names begin with Oracle exist and have the status Started then select the service and click Stop In particular ensure that the Oracle listener service is stopped c Click Close to exit the Services window About Oracle Universal Installer Oracle Universal Installer is a Java based graphical user interface GUI tool that enables you to install Oracle components from the installation media See Also Oracle Universal Installer and OPatch User s Guide for more information about Oracle Universal Installer Preinstallation 4 1 About Oracle Universal Installer 4 2 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide 5 Installation The development environment for the Oracle Procedural Gateway Visual Workbench for WebSphere MQ has two components PG4MQ Visual Workbench one for each developer which is installed on each developer s computer using the Oracle Universal Installer PG4MOQ Visual Workbench repository scripts shipped with the PG4MQ Visual Workbench installation media for message queuing systems This chapter
23. appears 7 Fillin the message buffer for each data profile Use the MIP Template drop down list to open the template for each data profile in the MIP 8 Click the Save icon in the tool bar to save your changes in the repository Figure 6 29 shows that code has been added to the ENQUEUE sample code to specify employee information Figure 6 29 Code Viewer with ENQUEUE Sample of Employee Information E Code Viewer Jol xj File Edit View Help TORR a PoE SS TERNI Gh Cay EE MIP Template ENQUEVE EMPLOYEE DECLARE message EMPLOYEE EMPLOYEE Typ h0bj PGM BQM QUEUE HANDLE Typ openOpts PGM BQM OPEN OPTIONS Typ enqueueOpts PGM BQM ENQUEUE OPTIONS Typ messageOpts PGM BQM MESSAGE PROPERTIES Typ queueName VARCHAR2 48 NULL msgid RAW 24 BEGIN Open the queue open pts open mode PGM BQM ENQUEUE MQ EMPLOYEE QOPEN queueName openOpts h0bj Populate message here message EMPLOYEENO 000001 message FIRSTNAME SCOTT message LASTNAME TIGER message BIRTHDATE 17 APR 67 message SALARY 50000 message BONUS 4000 message COMMISSION 10000 na Using a Template to Test the MIP Before testing a template ensure that the MIP has been compiled on the Oracle server where you will run the test and that you have added code for all the data profiles in the MIP See Also Compiling the MIP on page 6 26 and Completing the Templates on page 6
24. are encoded The default is AMERICAN _ AMERICA WE8EBCDIC37C in the format language territory charset If the remote system uses a different single byte character set then click Remote Singlebyte Character Set and select the appropriate set Remote Multibyte Character Set specifies the Oracle NLS name in which the remote system data for all multi byte character set fields are encoded The default is JAPANESE JAPAN JA16DBCS in the format language territory charset If the remote system uses a different multi byte character set then click Remote Multibyte Character Set and choose the appropriate set Local Multibyte Character Set specifies the Oracle NLS name in which the local system data for all multibyte character set fields are encoded The default is JAPANESE JAPAN JA16DBCS in the format language _ territory charset If the local system uses a different multibyte character set then click Local Multibyte Character Set and select the suitable set After entering the data definition information click Apply The PG4MQ Visual Workbench creates the data mapping to PL SQL in the repository and displays the copybook and resulting mapping information The following example shows a newly created data profile named EMPLOYEE and its copybook file Refer to Figure 6 5 6 6 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Creating a Data Profile Figure 6 5 Copybook and Mapping Information
25. binary integer default null expiration binary integer default null correlation varchar2 128 default null message type varchar2 256 default null response queue varchar2 128 default null delivery mode binary integer default null acknowledgement binary integer default null security id varchar2 128 default null The following table describes the parameters that need to be specified Parameter Description priority Specifies the message priority DEFAULT PRIORITY specifies the priority as the default priority of the message queuing system nnspecifies a numeric priority range from 0 up The smaller the number the higher the priority expiration Specifies when the message expires determining in seconds how long the message is available for dequeuing NEVER specifies that the message does not expire and is available on the queue for an unlimited time nnspecifies the number of seconds the message remains available on the queue Depending on how the queues and event handling of the message queuing system are configured the message queuing system might place expired messages on dedicated event queues correlation Specifies an application supplied identification for the message 7 16 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide PGM_BQM Package Parameter Description message_type Specifies an application supplied free format description of the message
26. database linken teet men eoe o eee me eite 5 5 Visual Workbench Repository Installation Tasks sese eee 5 5 Step 1 Enter the database connection information sss 5 5 Step 2 Check for existing Workbench Repository sse 5 5 Step 3 Check for required PL SQL packages sse eene 5 6 Step 4 Install the UTE PG package iieri tette 5 6 Step 5 Create the admin user and all repository tables sss 5 6 Step 6 Create public synonyms and development roles sss 5 6 After the Repository Is Created sse eee eene nennen 5 6 Remove the Visual Workbench Repository sess 5 7 Step 1 Enter the database connection information sss 5 7 Step 2 Check for existing Workbench repository sss 5 7 Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ Overview of PG4MQ Visual Workbench Development eese eee 6 1 Starting the PG4MQ Visual Workbench and Connecting to a Repository 6 3 Creating Data Profile eoe rele dte et ee dente reete e 6 5 Updating a Data Profile cin tanh ai UG n e p t t d te en eta eed rl R 6 8 Removing a Data Profile eate aetate ae ie vende tenere dede rtg Red 6 9 Creating a Message Queue Profile eee ne nenne nnne nnns 6 9 Updating a Message Queue Profile sse nennen n
27. for EMPLOYEE Data Profile ns Oracle Procedural Gateway Visual Workbench for WebSphere MQ File Edit View Tools Help pem J REPOSITORIES S bug stadf34 3H DATA PROFILES 1 COBOL id EMPLOYEE EMPMAP El ONELINE Eb PGCOMP s MESSAGE QUEUE PROFILES in IBM MQSERIES lt a INTERFACES Le PUSQL 01 EMPLOYEE 10 EMPLOYEENO PIC X B 10 FIRSTNAME PIC X 12 10 LASTNAME PIC X 12 10 BIRTHDATE PIC X 10 10 SALARY PIC 9999999V99 USAGE COMP 3 10 BONUS PIC 9999999V99 USAGE COMP 3 10 COMMISSION PIC S9999999V99 USAGE COMP 3 Select the PL SQL Mapping tab to see the PL SQL mapping for the new data profile Refer to Figure 6 6 Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 7 Updating a Data Profile Figure 6 6 PL SQL Mapping for EMPLOYEE Data Profile em Oracle Procedural Gateway Visual Workbench for WebSphere MQ olx File Edit View Tools Help O m TE AME ET WE TA Open Close Creste Create As Update Remove Select Add Refresh Gombe Test Wizard E3 REPOSITORIES f bug stadf34 PUSOL Mapping 2 5 DATA PROFILES aS COBOL PL SQL to Cobol field mappings EY EMPLOYEE Eb EMPMAP EMPLOYEE EMPLOYEE_Typ EMPLOYEE B PGCOMP FIRSTNANE H E MESSAGE QUEUE PROFILES LASTNAME SARAI IBM MQSERIES BIRTHDATE CHAR 10 BIRTHDATE Lis PUSAL COMMISSION NUMBER 9 2 COMMISSIOT Update Inserted Cobol Profile employee at the reposi
28. for WebSphere MQ Data Conversion Package COBOL Data Type Conversion COBOL Format Conversion PL SQL Naming Algorithm About the Message Interface Package MIP A MIP is a PL SQL package that provides a high level interface for Oracle applications to communicate with non Oracle message queuing systems Every time you create or update an interface profile the Visual Workbench generates a MIP from the interface profile information you supply Note Creating Interface Profiles and Generating the MIP on page 1 4 for more information Using the Generated MIP 7 1 About the Message Interface Package MIP Note The MIP described in this chapter is not compatible with the MIP released with the Visual Workbench release 4 0 1 1 1 and earlier You must modify applications that use the beta version of the MIP The Visual Workbench repository is not compatible with the MIP released with the Visual Workbench release 4 0 1 1 1 and earlier You must re create interface profiles that were created with the beta release What s in a MIP A MIP contains the code needed to interact with the message queuing system The database link connects the MIP to a procedural gateway that can communicate with a non Oracle message queuing system The MIP includes all the code needed to communicate with the gateway a A data profile defines the data types for messages exchanged between the Oracle and non Oracle applications The Vis
29. m TO n TIMES DEPENDING ON field 2 This causes conversion of at least m and not more than n instances of a set of PL SOL variables to or from a repeating group area within the remote host record The size of the area equals the group length multiplied by the repetition count contained in the named field The data conversion packages generated by the Visual Workbench use PL SQL RECORDs of TABLEs to implement an array like subscript on fields in a repeating group Because PL SQL supports a single dimension TABLE the Visual Workbench supports only a single level of an OCCURS group It does not support nested OCCURS groups Conversion and formatting are dictated by the COBOL data type of each sub field defined in the repeating group The data conversion packages generated by the Visual Workbench use a FOR LOOP algorithm with a range of 1 to the specified upper limit of TIMES RENAMES item 2 THRU item 3 A single PL SQL variable declaration corresponds to a RENAMES definition If all the subfields covered by a RENAMES definition are PIC X the PL SQL variable is data type VARCHAR2 Otherwise any non PIC X subfield causes the PL SQL variable data type to be RAW Lengths of renamed fields do not contribute to the overall parameter data length because the original fields dictate the lengths RENAMES item 2 WHEN item 3 value WHEN item 3 value is an Oracle extension to the COBOL copybook data definition as stored in the Visual Workbench repository T
30. manipulating raw data From SQL Plus as user SYS run the following statement SQL DESCRIBE UTL RAW COMPARE If the DESCRIBE statement is successful then your repository server already has UTL_ RAW installed and you can proceed to Step 4 If the DESCRIBE statement fails then install UTL RAW From SQL Plus as user SYS run the ut1raw sq1 and prvtrawb plb scripts that are in the ORACLE _HOME rdbms admin directory You must run the utlraw sql script first SQL gt utlraw sql SQL gt prvtrawb plb Step 4 Ensure that the DBMS_OUTPUT package is enabled The sample programs and installation verification programs on the distribution installation media use the standard DBMS_OUTPUT package From SQL Plus as user SYS run the following statement SQL gt DESCRIBE DBMS OUTPUT PUT LINE If the DESCRIBE statement is successful then your repository server has DBMS _ OUTPUT installed and you can proceed to Step 5 If the DESCRIBE statement fails then install DBMS OUTPUT Refer to your Oracle server DBA Step 5 Ensure that the caths sql script has been run Ensure that the caths sql script has been run on the Oracle database server before starting the gateway You can verify that this has been done by doing the following 1 Use SQL Plus to connect to the integrating server as user SYS 2 Run the following statement from SQL Plus SQL gt DESCRIBE HS FDS INST 5 4 Oracle Procedural Gateway Visual Workbench for WebSphere MQ I
31. page The Source section of the File Locations page lets you specify the source location that the installer will use to install PG4MQ Visual Workbench You should not have to edit the file specification in the Path field The default setting for this field points to the installer file on your PG4MQ Visual Workbench installation media The Name and Path fields in the Destination section of the File Locations page let you specify the destination for your installation Type in the Name and Path of your choice After you have set the fields in the File Locations page as necessary click Next to continue After loading the necessary information from the installation media the installer will display the Oracle Procedural Gateway for Messaging Queuing Visual Workbench Installer page Click Next to continue and display the Summary page The Summary page lets you review a tree list of options and componets for this installation Click Install to display the Install page which shows the status of the installation as it proceeds and also displays the location of the installer log file for this installation session Depending on your computer s CPU installation media drive and hard drive the installer might take quite some time to complete the installation process After the installer copies the Oracle software to your computer the Configuration Tools page is displayed and Oracle Net Configuration Assistant is then run to configure Oracle s networking
32. performed on the aligned data fields according to numeric data type for IN and OUT parameters See Also COBOL Data Type Conversion on page 7 21 for more information SYNCHRONIZED LEFT SYNCHRONIZED LEFT causes warnings to be displayed on the Visual Workbench during the generation of data conversion package and no realignment is performed Numeric conversion is performed on the aligned data fields according to numeric data type for IN and OUT parameters See Also COBOL Data Type Conversion on page 7 21 for more information PL SQL Naming Algorithm This section describes the PL SQL naming algorithm Delimiters Special COBOL characters in record group and element names are translated when data profiles are created using the Visual Workbench a Hyphen is translated to underscore Period is deleted Qualified Compound Names PL SQL variable names are fully qualified and composed from a PL SQL record name as the leftmost qualifier corresponding to level 01 or 77 COBOL record name a PL SQL nested record names corresponding to COBOL group names a PL SQL nested fields corresponding to COBOL elements of data type CHAR or NUMBER corresponding to nonrepeating COBOL elements TABLE corresponding to COBOL elements which fall within an OCCURS or OCCURS DEPENDING ON group COBOL repeating fields correspond to PL SQL nested RECORDs of TABLES When referencing PL SQL variables from cal
33. procedure fails then it raises a PL SOL exception Exceptions can be raised for various reasons such as an error occurring during conversion or a problem reported by the message queuing system The application calling the procedure is responsible for handling the exception Syntax PROCEDURE ENQUEUE payload IN type definition The following table describes the parameters that need to be specified Parameter Description payload Must be specified according to type definition NULL is an invalid value type definition See Also Type Definitions on page 7 14 and Data Conversion Package on page 7 19 Example In this example message contains an employee record defined by a data profile named EMPLOYEE Itis populated with data then passed to the basic ENQUEUE procedure of a MIP named HIRE 7 8 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide ENQUEUE Procedure When it is called the MIP converts the employee record according to the rules of the data profile and delivers the employee record to the specified queue using the ENQUEUE options and message properties of the message queue profile DECLARE message EMPLOYEE EMPLOYEE Typ BEGIN message EMPLOYEENO 123456 message FIRSTNAME SCOTT message LASTNAME TIGER message BIRTHDATE 01 01 71 message SALARY 50000 message BONUS 4000 message COMMISSION 0 HIRE ENQUEUE message COMMIT EXCEPTIO
34. product Oracle Net In the Oracle Net Configuration Assistant Welcome page click Perform typical configuration and then click Next Let the Oracle Net Configuration Assistant guide you through the rest of the installation until the End of Installation page is displayed The final page of the installer is the End of Installation page If your installation was successful then you can click Next to exit the installer Removing the Software This section describes how to use Oracle Universal Installer to remove Oracle components which removes them from the Oracle Universal Installer inventory instead of removing them manually WARNING If you delete an Oracle home manually for example by deleting the directory structure with Microsoft Windows XP Explorer then the components in the Oracle home remain registered in the Oracle Universal Installer inventory If you then attempt an installation in the same Oracle home then some or all of the components selected may not be installed because Oracle Universal Installer determines that they are already installed Removing Oracle Procedural Gateway Visual Workbench for WebSphere MQ using Oracle Universal Installer This section describes how to remove PG4MQ Visual Workbench with Oracle Universal Installer 5 2 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Installing the Visual Workbench Repository Starting the Oracle Universal
35. the associated type definitions for WebSphere MQ Parameter Restriction correlation Its value is limited to a length of 24 characters message type Its value is limited to a length of 8 characters msgid Its value is limited to a length of 24 bytes priority Its value range is between 0 and 9 queue name Its value is limited to a length of 48 characters response queue Its value is limited to a length of 48 characters security id Its value is limited to a length of 12 characters The MOSeries profile complies with these restrictions because the Visual Workbench verifies all input when entering the message queue profile properties You can however override the profile using the advanced ENQUEUE and DEQUEUE procedures possibly causing the MIP to raise an exception at runtime See Also OPEN OPTIONS Typ on page 7 14 for more information about overrides Data Conversion Package The data conversion package generated by the Visual Workbench provides PL SOL mapping for non Oracle data types It includes one or more user defined types in PL SQL that are mapped to the non Oracle data type and functions to convert between them Data conversion packages have two parts a specification and a body The specification declares the types functions and other information available for use by developers and the body provides the implementation for the specification You use the data conversion package and so does the generate
36. to the COBOL data type and two functions to convert between the PL SOL mapping data type and the COBOL data type This is the package specification for the package EMPLOYEE Copyright c 1999 Oracle Corporation All rights reserved PL SQL and Cobol Data Mapping Package Body EMPLOYEE PG DD release 4 0 1 0 0 i PGMAU release 8 0 4 1 0 Generated 11 MAR 99 at 15 06 33 by PGMADMIN CREATE or REPLACE PACKAGE EMPLOYEE IS 2lsebdseeLn2snistllore2lisSe2odsLusshLuseseLusem2lLresseulseouldaicniz EMPLOYEE public definitions Eug m e mecnm on tue e UE fom n mre G tere n sie rane i er Ene Mr cs gen ee i ca A TYPE MQGET BUFFER IS TABLE OF RAW 32767 INDEX BY BINARY INTEGER TYPE MQPUT BUFFER IS TABLE OF RAW 32767 NOT NULL INDEX BY BINARY INTEGER TYPE EMPLOYEE Typ is RECORD EMPLOYEENO CHAR 6 FIRSTNAME CHAR 12 LASTNAME CHAR 12 BIRTHDATE CHAR 10 SALARY NUMBER 9 2 BONUS NUMBER 9 2 COMMISSION NUMBER 9 2 FUNCTION raw2EMPLOYEE message IN MQGET BUFFER EMPLOYEE OUT EMPLOYEE Typ mipdiag IN CHAR RETURN INTEGER FUNCTION EMPLOYEE2raw EMPLOYEE IN EMPLOYEE Typ message OUT MQPUT BUFFER mipdiag IN CHAR RETURN INTEGER END EMPLOYEE The data conversion functions require these PL SQL packages DBMS OUTPUT a UTL PG 7 20 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide COBOL Data Type Conversion a UTL
37. 30 for more information Testing the ENQUEUE Procedure Template 1 Open the Interfaces folder 2 Open the PL SQL folder Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 29 Using the MIP Templates 3 Select a MIP by clicking it 4 Click the Test icon on the tool bar or the Test button on a panel The Message Interface Package Tester dialog box appears By default the PG4MQ Visual Workbench runs the test on the Oracle server where the repository resides To conduct the test on a different server select the Connection tab and modify the connection information 5 Onthe General panel choose MIP ENQUEUE operation using template code Refer to Figure 6 30 Figure 6 30 Dialog Box for Message Interface Package Tester f Message Interface Package Tester x Test Interface MQ_EMPLOYEE pu General Select Test Operation MIP ENQUEUE operation using template code MIP DEQUEUE operation using template code C Gateway ENQUEUE operation C Gateway DEQUEUE operation Test results A Run Test stop Rur Edit Template Close Revert Help 6 Click Run Test When the test completes the results appear in the Test Results window To see the test code for details of the test click Edit Code Testing the DEQUEUE Procedure Template 1 Open the Interfaces folder 2 Open the PL SQL folder 3 Select a MIP by clicking it 4 Click the Test icon on the tool bar or the Test butto
38. AISE END Advanced DEQUEUE Procedure The advanced DEQUEUE procedure retrieves a message from the message queue converts the payload value from native to PL SQL format according to the specifications of the data profile and returns the result in the payload output parameter The queue name and dequeue options parameters enable the calling application to control the DEQUEUE operation If a parameter or its fields are NULL then the DEQUEUE procedure uses the corresponding setting of the message queue profile associated with the MIP The message properties parameter returns the properties of the message as set by the enqueing application or the message queuing system The msgid parameter returns the message identifier of the dequeued message If the advanced DEQUEUE procedure fails thenit raises a PL SQL exception Exceptions can be raised for various reasons such as an error occurring during conversion or a problem reported by the message queuing system or providing an invalid value for one of the input parameter The application calling the procedure is responsible for handling the exception 7 12 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide DEQUEUE Procedure See Also PGM BOM Package on page 7 17 Syntax PROCEDURE DEQUEUE queue name IN VARCHAR2 dequeue options IN PGM BQM DEQUEUE OPTIONS Typ message properties OUT PGM BQM MESSAGE PROPERTIES Typ payload OUT t
39. ARCHAR in the example the Visual Workbench constructs a nested PL SOL record TYPE NAME typ is RECORD LENGTH NUMBER 4 0 LETTERS CHAR 30 ES TYPE is RECORD NAME NAME type i It is the client application s responsibility to extract NAME LENGTH characters from NAME LETTERS and assign the result to a PL SQL VARCHAR data type if VARCHAR is desired This requires specific knowledge of the remote host data Using the Generated MIP 7 21 COBOL Data Type Conversion PIC G PIC 9 Character set conversion is performed for single byte encoding a For remote host character data select a character set from the character set drop down list in the COBOL Data Definition dialog box for all single byte character fields in the data profile See Also Creating a Data Profile on page 6 5 for more information Forlocal Oracle character data set the language character set on the Oracle server for all character fields in the data profile Visual Workbench converts the COBOL G data type to a PL SQL VARCHAR2 data type of the same length allowing two bytes for every character position The alphanumeric and double byte character set editing field positions are listed in the following table Symbol Data Content B Blank one byte single byte character set two bytes double byte character set depending on USAGE 0 Zero one byte single byte character set Slash one byte single byte character set
40. BOL folder by clicking it once 4 Click the Create icon in the tool bar The Define COBOL Data Definition dialog box appears Refer to Figure 6 4 Figure 6 4 Dialog Box to Define COBOL Data Definition ewscosour AMERICAN AMERICA WESBEBCDIC37 CE JAPANESE JAPAN JAT6DBCS Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 5 Creating a Data Profile Terms Description Data Definition Name COBOL Copybook File COBOL Version National Language Support Name of the COBOL data definition to map to PL SOL The Data Definition Name Must be unique a Must be 1 to 22 characters in length a Can contain alphanumeric characters and the underscore character a Is not case sensitive Name of the COBOL copybook file containing the structure of the COBOL data definition The PG4MQ Visual Workbench uses this file to determine the correct mapping to PL SQL Use Browse to search your local computer for copybook files The sample copybook files are inORACLE_HOME pg4mqvwb demo cobol where ORACLE HOME is the directory in which the PG4MQ Visual Workbench is installed See Also Chapter 5 Installation for more information Must be IBMVSCOBOLII Specifies the character sets for data conversion between the local and remote systems Remote Singlebyte Character Set specifies the Oracle national language support NLS name in which the remote system data for all single byte character set fields
41. E MODE PGM BOM BROWSE HIRE DEQUEUE QUEUE2 dequeueOpts msgProps message msgid firstName message FIRSTNAME COMMIT EXCEPTION WHEN OTHERS THEN ROLLBACK RAISE END Using the MIP for WebSphere MQ This section describes how to use the MIP for WebSphere MQ PGM SUP Package Exceptions To access a WebSphere MQ system you must install the PGM SUP package before deploying the MIP PGM SUP contains definitions that are required for using the Oracle Procedural Gateway for WebSphere MQ When the Visual Workbench generates a MIP it uses the definitions of the PGM SUP package in the package body of the MIP Oracle applications using the MIP procedures do not access these definitions directly but need only be granted access to them See Also Preparing the MIP for Production on page 1 6 for more information When applications assign a value that is supported by the PGM_BOM package but not supported by WebSphere MQ the MIP procedures raise this exception for WebSphere MO INVALID MQSERIES VALUE EXCEPTION PRAGMA EXCEPTION INIT INVALID MQSERIES VALUE 20001 Applications are responsible for handling these exceptions See Also MIP Restrictions for more information 7 18 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Data Conversion Package MIP Restrictions The following table lists the restrictions that apply when using the MIP procedures or
42. ME Typ HOME ADDRESS HOME ADDRESS Typ DEPT CHAR 45 OFFICE ADDRESS OFFICE ADDRESS Typ JOBTITLE CHAR 20 However multiple nested groups might have the same name but have different sub fields as in the COBOL definition in Example 7 2 Example 7 2 COBOL definition 01 EMPREC 05 HIREDATE PIC X 8 05 BIRTHDATE PIC X 8 05 SKILL PIC X 12 OCCURS 4 05 EMPNO PIC 9 4 05 EMPNAME 10 FIRST NAME PIC X 10 10 LAST NAME PIC X 15 05 HOME ADDRESS 10 STREET PIC X 20 10 CITY PIC X 15 10 STATE PIC XX 10 ZIP 15 LEFTMOST FIVE PIC X 5 15 RIGHTMOST FOUR PIC X 4 05 DEPT PIC X 45 05 OFFICE ADDRESS 10 STREET PIC X 20 10 CITY PIC X 15 10 STATE PIC XX 10 ZIP 15 FIRST FIVE PIC X 5 15 LAST FOUR PIC X 4 05 JOBTITLE PIC X 20 Using the Generated MIP 7 27 PL SQL Naming Algorithm In a COBOL definition where multiple nested groups have the same name but different subfields the Visual Workbench alters the name of the PL SQL nested record type for each declaration in which the subfields differ in name data type or options as shown in Example 7 3 Example 7 3 COBOL definition SKILL Key BINARY INTEGER TYPE SKILL Tbl is TABLE of CHAR 12 INDEX by BINARY INTEGER TYPE EMPNAME Typ is RECORD FIRST NAME CHAR 10 LAST NAME CHAR 15 TYPE ZIP Typ is RECORD LEFTMOST FIVE CHAR 5 RIGHTMOST FOUR CHAR 4 TYPE HOME ADDRESS Typ is RECORD STREET CHAR 20 CITY CHAR 15 STATE CHAR 2 ZIP ZIP
43. MESSAGE PROPERTIES Typ 7 16 OPEN OPTIONS Typ 7 14 U updating data profile 6 8 message queue profile 6 13 MIP 6 19 UPS message queue profile 6 12 user name parameter for connecting 6 3 UTL PG package 5 6 6 34 7 20 A 1 A 2 UTL RAW package 5 4 5 6 6 34 7 21 A 1 A 2 utlpg sql script A 2 utlraw sql script 5 4 V viewing the generated code 6 17 Visibility DEQUEUE parameter for message queue profile 6 12 ENQUEUE parameter for message queue profile 6 11 visibility parameter 7 15 W Wait Interval Index 6 and no messages available exception DEQUEUE test 6 34 parameter for message queue profile 6 12 WebSphere MQ using the MIP for 7 18 WebSphere MQ Properties dialog box 6 10 Wizard PG4MOQ Visual Workbench 6 2 6 14
44. MM 7 22 BIDDER irte cde tton ee dee tecti tec ton ted S aie ets 7 23 COBOL Format Conversion sese e eee ee enne nnne nnn nenne nnne enne nnne 7 23 JUSTIFIED and JUSTIFIED RIGHT entente nennen nennen 7 23 JUSTIFIED LEED ect re re e eer edet aeree 7 23 OCCURS M TIMES aani teme hi set eee ede tica ibtd e eiie Re redd 7 24 OCCURS m TO n TIMES DEPENDING ON field 2 sse 7 24 RENAMES item 2 THRU item 3 entier terreni orta tke tenta latae robe danke eoe denk anota on 7 24 RENAMES item 2 WHEN item 3 Value essent tenerent ntntnee tenes 7 24 SYNCHRONIZED and SYNCHRONIZED RIGHT eee eene 7 25 SYNCHRONIZED LEF Diesin ie eee e ee Eee de tete te e te ia 7 25 PL SQL Naming Algorithm nneconnecdien eleemalueniami nte ntaie er epe eese ug 7 25 Delimitets onec aieo atit eise d ei oet od beo te bou Eve aeit 7 25 Qualified Compound Names sse eene enne nnen 7 25 Truncated and Non Unique Names sse nre nenne 7 26 D plicate Names sisisi hisas ete n nee ehe ht ue dka eue tbt id bove dcs 7 26 A Preparing the Production Oracle Server ostio E A 1 Verifying and Installing PL SQL Packages 1 15 eee enne nnn A 2 Removing the PL SQL Packages eee ette ee tee ie dre dette tie sedere A 3 Index vi Audience Preface The Oracle Procedural Gateway Visual Workbench for WebSphere MQ refer
45. MQ 6 13 Creating an Interface Profile and Generating a MIP Creating an Interface Profile and Generating a MIP After creating one or more data profiles and one message queue profile you are ready to create an interface profile To create an interface profile you use the PG4MQ Visual Workbench Wizard to select one or more data profiles and one message queue profile After you have selected the profiles the PG4MQ Visual Workbench uses the profile information to generate a MIP The MIP provides a PL SQL interface that defines the exchange and conversion of messages between your Oracle application and the non Oracle application You can start the Wizard by clicking the Wizard icon in the PG4MQ Visual Workbench tool bar to start the Wizard for a description of another way to start the Wizard The Welcome window appears Refer to Figure 6 13 See Also Alternative method for starting the Wizard to create an interface on page 6 16 Figure 6 13 Wizard Welcome Window Wizard Welcome Ea Welcome to the Wizard This wizard guides you through creating or updating an interface that contains code to simplify access to different Message Queuing middleware products from your Oracle application Click Next to begin creating or updating an interface Click here to skip this message next time Click Next to see the next Wizard window and select Create New Interface Refer to Figure 6 14 6 14 Oracle Procedural Gatew
46. N WHEN OTHERS THEN ROLLBACK RAISE END Advanced ENQUEUE Procedure The advanced ENQUEUE procedure converts the message content of payload according to the specifications of the data profile and sends the message to a queue The queue name enqueue operations and message properties parameters enable the calling application to control the ENQUEUE operation If a parameter or its fields are NULL then the ENQUEUE procedure uses the corresponding setting of the message queue profile associated with the MIP The msgid parameter returns the message identifier of the enqueued message It can be used in the advanced dequeue procedure to select the message to be retrieved If the advanced ENQUEUE procedure fails then it raises a PL SQL exception Exceptions can be raised for various reasons such as an error occurring during message conversion or a problem reported by the message queuing system Providing an invalid value for one of the input parameters also causes a PL SQL exception The application calling the procedure is responsible for handling the exception See Also PGM BOM Package on page 7 17 for more information Syntax PROCEDURE ENQUEUE queue name IN VARCHAR2 enqueue options IN PGM BQM ENQUEUE OPTIONS Typ message properties IN PGM BQM MESSAGE PROPERTIES Typ payload IN type definition msgid OUT RAW The following table describes the parameters that need to be specified Parameter Description queu
47. ORACLE Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide 10g Release 2 10 2 for Microsoft Windows 32 Bit B19082 01 August 2005 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide 10g Release 2 10 2 for Microsoft Windows 32 Bit B19082 01 Copyright 1996 2005 Oracle All rights reserved Primary Author Maitreyee Chaliha Contributing Author Li Te Chen The Programs which include both the software and documentation contain proprietary information they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright patent and other intellectual and industrial property laws Reverse engineering disassembly or decompilation of the Programs except to the extent required to obtain interoperability with other independently created software or as specified by law is prohibited The information contained in this document is subject to change without notice If you find any problems in the documentation please report them to us in writing This document is not warranted to be error free Except as may be expressly permitted in your license agreement for these Programs no part of these Programs may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose If the Programs are delivered to the United States Government or anyone licensing or using the
48. PERTIES Typ payload OUT EMPLOYEE EMPLOYEE Typ msgid OUT RAW END HIRE MIP Procedures The MIP contains QOPEN QCLOSE and both a basic and an advanced version of the ENQUEUE and DEQUEUE procedures for each data profile associated with the MIP a The basic version takes all options and properties from the message queue profile It requires one argument a message content buffer identified as payload in the syntax described in this chapter Theadvanced version has additional parameters that let the calling application control how to send or retrieve a message The calling application can override the options and properties specified in the message queue profile and can set other options See Also ENQUEUE Procedure on page 7 7 and DEQUEUE Procedure on page 7 11 for more information Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide ENQUEUE Procedure QOPEN Procedure The QOPEN procedure establishes access to a queue It returns a queue handle that is used as an input parameter in the advanced versions of the ENQUEUE and DEQUEUE procedures It is not necessary to perform a QOPEN before calling the basic ENQUEUE or DEQUEUE procedures The basic ENQUEUE and DEQUEUE procedures open the queue before sending and retrieving messages QOPEN Syntax PROCEDURE QOPEN queue name IN VARCHAR2 open options IN PGM BQL OPEN OPTIONS Typ queue handle OUT PGM BOL QUEUE HANDLE Typ
49. PTION This procedure reads the next message from the queue defined for this interface and converts it to the PL SQL type EMPLOYEE EMPLOYEE Typ output parameter named payload The procedure uses the options and properties defined by the message queue profile HR NOTES If the function fails an exception is raised RETURNS void Using the Generated MIP 7 5 MIP Procedures PROCEDURE DEQUEUE payload OUT EMPLOYEE EMPLOYEE Typ NAME Procedure DEQUEUE Get a message EMPLOYEE from the queue advanced DESCRIPTION This routine reads the next message from the queue defined for this interfaceand converts it to the PL SQL type EMPLOYEE EMPLOYEE Typ output parameter named payload Using the input parameters queue handle and dequeue options the caller controls how the dequeue operation should operate If any of the input parameters or their respective fields are NULL the function will use the settings defined by the message queue profile HR The properties of the dequeued message are returned in the message properties output parameter The message identification as generated by the message queuing System for the message is returned in the msgid output parameter NOTES If the function fails an exception is raised RETURNS void PROCEDURE DEQUEUE queue handle IN PGM BQM QUEUE HANDLE Typ dequeue options IN PGM BOM DEQUEUE OPTIONS Typ message properties OUT PGM BQM MESSAGE PRO
50. Programs on behalf of the United States Government the following notice is applicable U S GOVERNMENT RIGHTS Programs software databases and related documentation and technical data delivered to U S Government customers are commercial computer software or commercial technical data pursuant to the applicable Federal Acquisition Regulation and agency specific supplemental regulations As such use duplication disclosure modification and adaptation of the Programs including documentation and technical data shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement and to the extent applicable the additional rights set forth in FAR 52 227 19 Commercial Computer Software Restricted Rights June 1987 Oracle Corporation 500 Oracle Parkway Redwood City CA 94065 The Programs are not intended for use in any nuclear aviation mass transit medical or other inherently dangerous applications It shall be the licensee s responsibility to take all appropriate fail safe backup redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes and we disclaim liability for any damages caused by such use of the Programs Oracle JD Edwards PeopleSoft and Retek are registered trademarks of Oracle Corporation and or its affiliates Other names may be trademarks of their respective owners The Programs may provide links to Web sites and access to con
51. Q For more information see the Oracle Procedural Gateway for WebSphere MQ Installation and User s Guide for Microsoft Windows for your platform This release supports automatic data conversion between only two types of application Oracle PL SQL applications and IBM VS COBOLII applications You can add other data conversions manually This chapter contains the following sections Hardware Requirements Software Requirements Hardware Requirements Processor Memory Disk Space The hardware requirements are described in this section An Intel Pentium III processor is required A minimum of 128 MB memory is required 256 MB of memory is recommended The PG4MQ Visual Workbench requires 300 MB one Visual Workbench per developer Software Requirements The system software configuration described in these requirements is supported by Oracle as long as the underlying system software products are supported by their respective vendors Verify the latest support status with your system software vendors PG4MQ Visual Workbench Components The PG4MQ Visual Workbench development environment has two components Requirements 3 1 Software Requirements a Visual Workbench client and server Visual Workbench repository You install the PG4MQ Visual Workbench on your Microsoft Windows 32 bit workstation The PG4MQ Visual Workbench repository may reside on a different platform than the PGA4MO Visual Workbench
52. Templates on page 6 28 for more information about MIP templates 6 16 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Creating an Interface Profile and Generating a MIP The following example shows generated code packages for a MIP named DO DEPOSIT Refer to Figure 6 16 Figure 6 16 DO DEPOSIT MIP Example pa Oracle Procedural Gateway Visual Workbench for WebSphere MQ File Edit View Tools Help id i Ly BOX CIGSe HERE E z SCEA Add efresr REPOSITORIES Li bug stadf34 C DATA PROFILES 2 5 COBOL DEPOSIT Eb EMPLOYEE Eb EMPMAP ONELINE i Eb PGCOMP ENQUEUE DEPOSIT MIP Template 07 jul 05 14 49 EE MESSAGE QUEUE PROFILES DEQUEUE DEPOSIT MIP Template 07 jul 05 14 49 Gal IBM MOSERIES DO_DEPOSIT Message Interface Package 07 jul 05 14 49 te DEPOSIT QUEUE DEPOSIT Cobol Mapping 07 jul 05 14 48 3 MQ_PROFILE1 3 UPS x INTERFACES eg PLISQL b DEPOSIT view Code Compile To see the MIP or template code select the PL SQL Code tab Select an entry from the PL SQL code list by clicking its name once andthen clicking View Code The Code Viewer window appears The example shown in Figure 6 17 illustrates the MIP specification for the MIP named DO DEPOSIT Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 17 Updating a MIP Figure 6 17 Code Viewer Window n Oracle P
53. able in HTML format and contains markup to facilitate access by the disabled community Accessibility standards will continue to evolve over time and Oracle is actively engaged with other market leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers For more information visit the Oracle Accessibility Program Web site at http www oracle com accessibility vii Accessibility of Code Examples in Documentation Screen readers may not always correctly read the code examples in this document The conventions for writing code require that closing braces should appear on an otherwise empty line however some screen readers may not always read a line of text that consists solely of a bracket or brace Accessibility of Links to External Web Sites in Documentation This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites TTY Access to Oracle Support Services Oracle provides dedicated Text Telephone TTY access to Oracle Support Services within the United States of America 24 hours a day seven days a week For TTY support call 800 446 2398 Product Name The complete name for this product is Oracle Procedural Gateway Visual Workbench for WebSphere MQ In this document this product is also called the Visua
54. acle Procedural Gateway for WebSphere MQ for message queuing For detailed information about Oracle Procedural Gateways for message queuing systems and about message queuing systems in general refer to the gateway installation guide for your platform The following topics are included Message Queuing Systems The Oracle Procedural Gateway for Message Queuing Systems The PG4MO Visual Workbench Development Environment Message Queuing Systems Message queuing systems enable communication between applications In a message queuing system an application stores messages in a queue that is accessible to another application One application sends a message to a queue and the other application asynchronously retrieves the message and processes the information it contains The Oracle Procedural Gateway for Message Queuing Systems Oracle Procedural Gateways for message queuing systems enable Oracle applications to send messages to and retrieve messages from message queuing systems The gateway converts Oracle remote PL SQL procedure calls into calls understood by the message queuing system s interface Using the gateway Oracle applications can access non Oracle applications that are enabled for message queuing The Oracle Procedural Gateway for message queuing provides the basic mechanism that enables Oracle applications to communicate with non Oracle applications using message queuing systems To write these applications without a
55. al Workbench produces the MIP itself data conversion code and templates that show how to access the message queuing system using the MIP Using the Visual Workbench you can install the generated MIP on any Oracle server where your application is running See Also Creating an Interface Profile and Generating a MIP on page 6 14 and About the Message Interface Package MIP on page 7 1 for more information Testing the Gateway The PG4MQ Visual Workbench produces PL SQL test code in addition to the generated MIP Use the test code to verify the interaction with the procedural gateway before you compile the MIP on the target server Using the test code before compiling the MIP helps identify any problems in the interaction with the gateway It is easier to identify such problems before compiling than when testing the MIP itself See Also Testing the Gateway on page 6 22 for more information Compiling the MIP The MIP and data conversion code must be compiled on an Oracle server before you can use them in your application After completing the development and testing use the Visual Workbench to compile the generated MIP on the server you choose for compiling See Also Compiling the MIP on page 6 26 for more information Using the MIP Templates The PG4MQ Visual Workbench produces templates for the generated MIP After you have verified the test code and compiled the interface you can complete
56. ata conversion package and 7 19 DEPOSIT template 6 28 ENQUEUE_OPTIONS_Typ 7 15 inthe MIP 7 7 MESSAGE PROPERTIES Typ 7 16 naming and name resolution 7 8 on the General panel 6 22 procedure 6 28 6 30 7 2 7 8 7 8 procedure advanced 7 9 sample code example 6 31 sample ENQUEUE DEPOSIT template code 6 29 template 6 30 testing the ENQUEUE procedure template 6 32 testing the procedure template 6 32 testing through the gateway 6 22 Visibility parameter 6 11 enqueue operations parameter 7 9 enqueue options parameter 7 15 ENQUEUE OPTIONS Typ type definition 7 15 error compilation 6 27 error message ORA 01000 2 2 ORA 20004 2 2 Error Messages dialog box 6 27 exceptions no messages available DEQUEUE test 6 34 PL SOL 7 17 Expiration parameter for message queue profile 6 11 F file tnsnames ora 6 34 file transfer program 5 4 FILLER 7 23 format conversion COBOL 7 23 G gateway testing 6 22 gateway test code 6 22 gateway test default 6 22 General panel 6 4 generated code package 6 17 generated code viewing 6 17 generated package 6 27 GRANT statement syntax 6 35 H hardware requirements 3 1 HIRE interface profile 7 3 hostfile 6 3 host name parameter for connecting 6 3 HR message queue profile 7 3 IBMVSCOBOLII 6 6 IN parameter 7 23 7 25 initialization parameter CLOSE CACHED OPEN CURSOR 2 2 input parameter 7 7 7 9 7 10 7 12 7 13 7 16 installation scr
57. ata structures used in the MIP procedures and the supporting packages that define these structures OPEN OPTIONS Typ This type defines the open options by an application when it calls QOPEN This structure type is used for the open options parameter in the open call Syntax TYPE OPEN OPTIONS Typ IS RECORD open mode binary integer default null The open mode parameter specifies in which mode the queue is open ENQUEUE puts messages into the queue can be used in combination with REMOVE and BROWSE REMOVE reads and removes the message from the queue can be used in combination with ENQUEUE BROWSE reads a message from the queue but does not remove it can be used in combination with ENQUEUE a NULL is an invalid value You must set the open mode when you call the advanced ENQUEUE and DEQUEUE procedures The advanced DEQUEUE procedure does not use the value specified in the Dequeue Mode field in the Visual Workbench You must specify REMOVE or BROWSE dequeue mode in the advanced DEQUEUE procedure 7 14 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Type Definitions ENQUEUE OPTIONS Typ This type defines the ENQUEUE options that can be specified by the calling application The structure type is used for the enqueue options parameter of the advanced ENQUEUE procedure If the fields of this parameter contain values then they override the settings of the message queue p
58. ay Visual Workbench for WebSphere MQ Installation and User s Guide Creating an Interface Profile and Generating a MIP Figure 6 14 Wizard Interface Window fj interface Wizard x Cancel Help Back Next gt Finiek Which action can I help you with Choose an action then click Next Create New Interface Update Existing Interface Click Next to begin the Wizard s step by step instructions to Enter a PG4MQ Visual Workbench repository name Name the interface profile which becomes the name of the generated MIP Specify a database link Add one data profile Add one message queue profile Click Finish after verifying your selections signaling the PG4MQ Visual Workbench to generate the MIP code If you need help using the Wizard then click Help Alternative method for starting the Wizard to create an interface Another way to start the Wizard to create an interface is to 1 2 3 4 Open a PG4MQ Visual Workbench repository Open the Interfaces folder Select the PL SQL folder Click the Create icon on the tool bar The Create New Interface window of the Wizard appears Refer to Figure 6 15 Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 15 Creating an Interface Profile and Generating a MIP Figure 6 15 Create New Interface Window of Wizard Interface Wizard Lx Create New interface The Wizard is going to guide you through the following steps 1
59. box 6 Change the User Name Password Host Name Port Number and SID fields to specify the production Oracle server 7 Decide if you want to grant execution privileges to other Oracle users for this MIP before you compile or after you compile the MIP 6 32 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Preparing the MIP for Production Grant before compiling Select the Options tab of the Message Interface Package Compiler dialog box and select the Grant public access to the packages option Go to step 8 Grant after compiling Continue to step 8 to compile the MIP See Also Granting Execution Privileges to a MIP on page 6 35 for more information 8 Click Compile Granting Execution Privileges to a MIP The MIP packages are compiled on the production Oracle server in the schema of the user specified with the connection information If the compiled MIP packages are used by other Oracle users or by applications that are run by another user then you must grant execution privileges to those users You can grant execution privileges during the MIP compilation procedure as described under Compiling the MIP for the Production Oracle Server on page 6 35 or use the GRANT statement at the computer where the production Oracle server resides GRANT has this syntax GRANT EXECUTE ON MIP name TO user name PUBLIC See Also Oracle Database SQL Reference for mo
60. ch repository Creating a Message Queue Profile on page 6 9 for details See Also Creating a Message Queue Profile on page 6 9 for details Creating Interface Profiles and Generating the MIP Using the Visual Workbench Wizard you combine one data profile and one message profile to define or update an interface profile The interface profile Defines how messages are exchanged with the non Oracle application using the gateway and the message queuing system Defines how message data is converted for communication between the applications The Visual Workbench uses the interface profile information to generate a MIP a PL SQL package that provides a high level interface between Oracle applications and non Oracle message queuing applications To create an interface profile you specify One data profile One message queue profile A unique name for the interface 1 4 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide The PG4MQ Visual Workbench Development Environment The database link for communication between Oracle and a message queuing system through the gateway After you have defined the interface profile the Visual Workbench automatically generates a MIP based on the interface profile information you specified A MIP is a PL SQL interface to send messages to and retrieve messages from a non Oracle message queuing system When you generate a MIP the Visu
61. ct dialog box appears Refer to Figure 6 1 Figure 6 1 Dialog Box to Connect to the Repository Server S Connect to Repository Server x Connect to Repository Server User Name Password pee 71 Cancel Host Name Port Number ism Oracle SID rns Modify the fields in the Connect dialog box to identify the computer where the PG4MOQ Visual Workbench repository is located then click OK Description Terms User Name Password Host Name Port Number Oracle SID Name under which you logged on To use the PG4MQ Visual Workbench you must have an account on the Oracle server where the PG4MQ Visual Workbench repository resides The default is the PGMADMIN account which is generated by the repository installation scripts Password associated with the user name Name of the computer where the PG4MQ Visual Workbench repository resides The default is the name of the computer from which you are running the PG4MQ Visual Workbench If you do not know the name of the host computer then ask your DBA Depending on how your Microsoft Windows 32 bit workstation is set up the host file may not contain the host name If the host file does not contain this name then you must enter the IP address in the Host Name field or edit the host file to include the host name Port number of the Oracle server where the repository resides It is usually the default of 1521 the port number used for standard Oracle installations If you ha
62. d MIP Youcan use the generated PL SQL type in the data conversion package specification to fill out information on a message The message is then passed to the ENQUEUE operation and sent over the message queuing system to the non Oracle application It is the same structure you use to retrieve messages from the non Oracle application through the DEQUEUE operation and to interpret the message information The generated MIP uses the data conversion functions to convert data Because the data conversion is handled in the generated MID you need not call these conversion functions directly Read the generated package specification to learn about the PL SQL types that were created In most cases you need not look at the package body 01 EMPLOYEE 10 EMPLOYEENOPIC X 6 10 FIRSTNAME PIC X 12 10 LASTNAME PIC X 12 10 BIRTHDATE PIC X 10 10 SALARY PIC 9999999V99 USAGE COMP 3 Using the Generated MIP 7 19 Data Conversion Package 10 BONUS PIC 9999999V99 USAGE COMP 3 10 COMMISSION PIC 9999999V99 USAGE COMP 3 Creating a Data Profile on page 6 5 describes how to use the Visual Workbench to create a data profile Use the Visual Workbench Wizard to add them to an interface profile See Also Creating an Interface Profile and Generating a MIP on page 6 14 for more information The corresponding generated data conversion package EMPLOYEE for COBOL data profile EMPLOYEE contains the PL SQL type used to map
63. data passed from the application is stripped of its right most blanks and are left padded as required and are sent to the remote host Output data is aligned as it is received from the remote host and left padded with blanks as required and are sent to the application See Also COBOL Data Type Conversion on page 7 21 for more information JUSTIFIED LEFT JUSTIFIED LEFT causes warnings in the Visual Workbench when the data conversion package is being generated No alignment is performed The remote host transaction data is converted as a PL SQL CHAR data type according to character data type for both IN and OUT parameters Note COBOL Data Type Conversion on page 7 21 for more information Using the Generated MIP 7 23 COBOL Format Conversion OCCURS n TIMES OCCURS n TIMES causes conversion of n instances of a set of PL SQL variables to or from a repeating group area within the remote host record The size of the area equals the group length multiplied by n repetitions The data conversion packages generated by the Visual Workbench use PL SQL RECORDs of TABLEs to implement an array like subscript on fields in a repeating group Because PL SQL supports a single dimension TABLE the Visual Workbench supports only a single level of an OCCURS group It does not support nested OCCURS groups Conversion and formatting are dictated by the COBOL data type of each subfield defined in the repeating group OCCURS
64. de Use this code to test the message queuing gateway associated with the MIP To test the ENQUEUE operation the gateway test code sends a string of numbers through the message queuing gateway to the queue specified by the message queue profile The result of the test appears on a status panel 6 20 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Testing the Gateway To test the DEQUEUE operation the gateway test code tries to retrieve a message through the gateway from the message queue specified by the message queue profile The result of the test appears on a status panel Note The gateway test code does not call the data conversion package or the MIP To test the gateway 1 Open the PG4MQ Visual Workbench repository 2 Openthe Interfaces folder 3 Open the PL SQL folder 4 Select a MIP by clicking it 5 Click the Test icon in the tool bar or the Test button on a panel The Message Interface Package Tester dialog box appears By default the PG4MO Visual Workbench runs the gateway test on the computer where the repository resides To conduct the test on a different server select the Connection tab and modify the connection information 6 Onthe General panel select Gateway ENOUEUE Operation Refer to Figure 6 21 Figure 6 21 Dialog Box for Message Interface Package Tester I Message Interface Package Tester LX Test Interface DO DEPOSIT g Connection
65. development tool like the PG4MQ Visual Workbench you must Write PL SQL code to call the gateway remote procedure calls that provide communication with the message queuing system Often write code to handle incompatible data types between Oracle applications and target applications The PG4MQ Visual Workbench automates these tasks for developers Introduction 1 1 The PG4MQ Visual Workbench Development Environment The PG4MQ Visual Workbench Development Environment The PG4MQ Visual Workbench simplifies the development work necessary to access message queuing applications through the gateway The PG4MQ Visual Workbench does this by handling data conversion between incompatible data types in messages exchanged between Oracle applications and non Oracle message queuing applications The PG4MQ Visual Workbench has two components Visual Workbench Visual Workbench repository Use the Visual Workbench to develop interface profiles that contain the information necessary to communicate with other message queuing applications The Visual Workbench stores the interface profile information in the Visual Workbench repository Using the Visual Workbench you compile the interface profile producing a MIP that contains the PL SQL code needed for Oracle applications to communicate with non Oracle message queuing applications in a run time environment You provide MIP templates as a starting point for your application to use the MIP and t
66. e Oracle Procedural Gateway Visual Workbench for WebSphere MQ is used with the Oracle Procedural Gateway for message queuing products This release supports release 2 10 2 of the Oracle Procedural Gateway for WebSphere MQ For more information see the Oracle Procedural Gateway for WebSphere MQ Installation and User s Guide for Microsoft Windows for your platform This release supports automatic data conversion between only two types of application Oracle PL SQL applications and IBM VS COBOLII applications You can add other data conversions manually This chapter contains the following sections ProductSet Migration Considerations Changes and Enhancements Release 10 2 0 1 0 a Known Problems These components are included on the product installation media Release Product Number Oracle Universal Installer 2 0 1 6 0 Oracle Procedural Gateway Visual Workbench for WebSphere MO 10 2 0 1 0 Oracle Net 10 2 0 1 0 This release replaces Oracle Procedural Gateway Visual Workbench for WebSphere MQ release 9 0 1 1 1 and earlier which used a client server system See Also Figure 1 1 Development Environment Migration Considerations The message interface package MIP described in Chapter 7 Using the Generated MIP is not compatible with the MIP generated by the Visual Workbench release 9 0 1 and earlier You must modify applications that use release 9 0 1 and earlier releases of the MIP Release Informati
67. e name Name of the queue to send the message enqueue options Defines the ENQUEUE options that can be specified by the calling application See Also ENOQUEUE OPTIONS Typ on page 7 15 Using the Generated MIP 7 9 ENQUEUE Procedure Parameter Description message Defines the message options for a message See Also MESSAGE properties PROPERTIES Typ on page 7 16 payload Must be specified according to the type definition NULL is an invalid value type definition See Also Type Definitions on page 7 14 and Data Conversion Package on page 7 19 msgid Identifier generated by the message queuing system for the message Example In this example the queue to which messages are to be sent is opened with QOPEN The openOpts open mode parameter is set to ENQUEUE to open the queue for input This parameter must be specified A NULL value is invalid A handle to the queue is returned and is used as an input parameter in each ENQUEUE call In this example the message contains an employee record defined by a data profile named EMPLOYEE It is populated with data and then passed to the advanced ENQUEUE procedure of a MIP named HIRE The priority and expiration of the message are set by the calling application and passed in using the values specified by enqueueOpts and msgProps After the messages are enqueued the QCLOSE procedure is called When called the MIP converts the employee record according to the specif
68. e named HIRE a message queue profile named HR that is configured for WebSphere MQ and a data profile named EMPLOYEE Copyright c 1999 Oracle Corporation All rights reserved Message Interface Package specification HIRE for WebSphere MQ Using Database Link GTWMQ WORLD Using Message Queue Profile HR Queue QUEUE1 Security ID B Enqueue Visibility On Commit Dequeue Visibility On Commit Dequeue Mode Remove Wait No Wait Correlation Priority Default Expiration Never Message Type Response Queue QUEUE1 Delivery Mode Not Persistent Acknowledgement None Using the Generated MIP 7 3 About the Message Interface Package MIP Using Data Profile s EMPLOYEE MIP procedures Procedure QOPEN Establish access to a queue Procedure QCLOSE Relinquish access to a previously opened queue Procedure ENQUEUE Put a EMPLOYEE message on the queue basic Procedure ENQUEUE Put a EMPLOYEE message on the queue advanced Procedure DEQUEUE Get a EMPLOYEE message from the queue basic Procedure DEQUEUE Get a EMPLOYEE message from the queue advanced Generated 16 MAR 1999 10 51 by pgmadmin CREATE OR REPLACE PACKAGE HIRE AS NAME Procedure QOPEN Establish an access to a queue object DESCRIPTION This function takes on input the name of the queue to open and an open option parameter The open mode fie
69. e procedure uses the options and properties defined by the message queue profile HR NOTES If the procedure fails an exception is raised RETURNS void PROCEDURE ENQUEUE payload IN EMPLOYEE EMPLOYEE Typ i a i 39RO BO ORO i 3B 03K i a 0B GO NAME Procedure ENQUEUE Put a EMPLOYEE message on the queue advanced DESCRIPTION This procedure takes on input a payload parameter of PL SQL type EMPLOYEE EMPLOYEE Typ converts it to the native format understood by the remote application and sends it to the queue defined for this interface Using the input parameters queue handle enqueue options and message properties the caller controls how the enqueue operation should operate If the input parameters or their respective fields are null the procedure uses the options and properties as defined by the message queue profile HR The message identification as generated by the message queuing System for the message is returned in the msgid output parameter This identifier can be used to identify the message at dequeue time NOTES If the procedure fails an exception is raised RETURNS void PROCEDURE ENQUEUE queue handle IN PGM BOM QUEUE HANDLE Typ i i i 333 03RO 03 E O3 enqueue options IN PGM_BQM ENQUEUE OPTIONS Typ message properties IN PGM BOM MESSAGE PROPERTIES Typ payload IN EMPLOYEE EMPLOYEE Typ msgid OUT RAW NAME Procedure DEQUEUE Get a message EMPLOYEE from the queue basic DESCRI
70. e re tbe reir debe Pu 1 5 Preparing the MIP for Production sese eee nennen 1 6 The PG4MQ Visual Workbench Repository sese een nnns 1 6 Release Information ProductSet nosonpooe noU ben hb p ret enc ec ae Rh a e Septet tese dies 2 1 Migration Corisideratiofis xtd e enne te Ded i e ei Ee ER Ee e En 2 1 Changes and Enhancements Release 10 2 0 1 0 sees enne 2 2 Accessing release 10 2 0 1 0 and all earlier releases of PG4MQ Visual Workbench Repositories 2 2 Known Probles n une ene ue E rte i ee Ed 2 2 Maximum Open Cursors n anteire hice tte oa bad pe vo beo t n a Hn eda Re 2 2 Incorrect Precision of PL SQL Data Types sese eene nnne nennen 2 2 The MIP Is Too Large ete ettet t eee te trim tee rte epp eee rerit pna 2 2 Requirements Hardware Requirements et retine eerie br ees Erit eode e ice deer E ead 3 1 PtOCOSSOE nr ete ted ee te ERI ER te Ee a Ee EAR Rr Eee ERROR de edu eeu ee d e epe ee M sees concedes 3 1 MCI OLY d hice occ Gi ates cules c bs cs LLOER EREA E E E estan ieee ies det ss Ie eles tale Bieceds 3 1 Disk Spacexc utente e para ater tee ret Wve deep pert eerte eee edt Srdiees 3 1 Software Requirements enn ou eee a ee tete te rte C beide repe tense 3 1 PG4MQ Visual Workbench Components sse eene een nnne 3 1 Operating System iuinceeeeidm dee ee ata bre Ht s de ani 3 2 Oracle Procedural Gateway eee ete tere feeder rite dent
71. er darent deg rie ido 3 2 Oracle Servers d n e tte ee ee ie el etus 3 2 Pile Lranstfer 23 neu ete etie een NM RT OE M SEES AU E EA MAS OEE 3 2 Preinstallation Preinstallation T sKs LEO eA LE is o ente eec OE Sieh 4 1 About Oracle Universal Installer eeeeseeeeeeeeeeene eene nnne e a i nenas innen 4 1 Installation Installation oxi Enni tn bene emite 5 1 Starting the Oracle Universal Installer sse 5 1 Installing Oracle Procedural Gateway Visual Workbench for WebSphere MQ 5 1 Removing the Software ineo neo emet eedem d er e tene id doit e Eee kis d 5 2 Removing Oracle Procedural Gateway Visual Workbench for WebSphere MQ using Oracle Universal Installer 5 2 Starting the Oracle Universal Installer seseeeeee eee nen 5 3 Visual Workbench Installation Complete eese eee eee enne 5 3 Installing the Visual Workbench Repository sse eene een enne 5 3 Preinstallati r Tasks eaeneteaniie nd e diete dame EEE 5 3 Step 1 Choose a repository server iiai aiaee aa e Eea aE eneit east 5 4 Step 2 Locate the installation scripts sse eere 5 4 Step 3 Ensure that the UTL RAW package is installed cece cece sss 5 4 Step 4 Ensure that the DBMS_OUTPUT package is enabled sss 5 4 Step 5 Ensure that the caths sql script has been run s sss 5 5 Step 6 Create a
72. eters In this example dequeueOpts directs the DEQUEUE procedure to get the next message from the queue but not remove it from the queue The DEQUEUE procedure is called in a loop to retrieve a message from the specified queue using the passed in queue name and DEQUEUE mode taking the other options and properties from the message queue profile When the exception POM BOM NO MORE MESSAGE is raised OCLOSE is called and the transaction is committed The message is converted from native to PL SQL format according to the specifications of the data profile and returned in the output parameter message In this example the application uses the dequeued message to obtain the employee s first name DECLARE message EMPLOYEE EMPLOYEE Typ dequeueOpts PGM BOM DEQUEUE OPTIONS Typ msgProps PGM BOM MESSAGE PROPERTIES Typ msgid RAW 24 Using the Generated MIP 7 13 Type Definitions openOptions PGM BOM OPEN OPTIONS Typ queueName VARCHAR2 48 queueHandle BINARY INTEGER BEGIN queueName emp openOpts open mode PGM BOM REMOVE HIRE QOPEN queueName openOpts queueHandle LOOP HIRE DEQUEUE queueHandle queueName dequeueOpts msgProps message count msgid INSERT into emp firstname VALUES message firstname END LOOP EXCEPTION WHEN PGM BQM NO MORE MESSAGES THEN HIRE QCLOSE queueHandle COMMIT WHEN OTHERS THEN HIRE QCLOSE queueHandle ROLLBACK RAISE END Type Definitions This section describes the d
73. fter compiling the MIP you can use the template code to test it Note Using the MIP Templates on page 6 28 for more information about MIP templates To compile a MIP 1 Open the PG4MQ Visual Workbench repository 2 Open the Interfaces folder 3 Open the PL SQL folder 4 Select the MIP to compile by clicking it 5 Click the Compile icon in the tool bar or the Compile button on a panel The Message Interface Package Compiler dialog box appears 6 24 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Compiling the MIP Figure 6 25 Dialog Box for Message Interface Package Compiler S Message Interface Package Compiler ix Compile Interface DO DEPOSIT amp Compile At Production System Server User Name Host Name ITDEV SUN7 Port Number 521 Oracle SID foRcL Compilation results ap Compile Stop Compile Close Revert Help By default the PG4MQ Visual Workbench compiles the MIP on the Oracle server where the repository resides Verify that the connection information is correct for the Oracle server on which you will compile the MIP and modify it if necessary 6 Select the Options tab if you want to set a compile option that removes generated packages if there are compile errors 7 Click Compile The Compilation Results window shows the progress of the compile After the MIP is compiled with no errors it is ready for use
74. fter you have created the repository See Also Installing the Visual Workbench Repository Installing the Visual Workbench Repository Install the Visual Workbench repository following the steps in this section Preinstallation Tasks This section describes the preinstallation tasks Step 1 Choose a repository server A repository server is an Oracle integrating server on which the Visual Workbench repository is installed Installation 5 3 Installing the Visual Workbench Repository Step 2 Locate the installation scripts The Visual Workbench repository installation scripts are installed with the Visual Workbench If the repository is to be installed on the same computer as Visual Workbench then your repository server already has all the required installation scripts Proceed to Step 3 1 Create a directory on the repository server that is to be the script directory For example gt md ORACLE HOMEMpg4mq admin Vrepo 2 Use a file transfer program to transfer the repository zip file reposXXX zip where XXX is the release number or move all script files with the suffix sql from the script file directory NT ORACLE_HOME pg4mqvwb server admin on the Visual Workbench computer to the script file directory on the repository server computer Step 3 Ensure that the UTL RAW package is installed All data mapping packages generated by the Visual Workbench use the UTI RAW package which provides routines for
75. guides you through the installation of the Oracle Procedural Gateway Visual Workbench for WebSphere MO including detailed installation steps The following topics are included Installation Installation Removing the Software Visual Workbench Installation Complete Installing the Visual Workbench Repository This section describes the installation steps Starting the Oracle Universal Installer To start the installer insert the PG4MQ Visual Workbench installation media into your computer s installation media drive and perform the following steps 1 2 Choose Start Run Enter drive setup exe in the Open field of the Run dialog box where drive is the drive designation for the installation media drive that contains the PG644MQ Visual Workbench installation media For example if your installation media drive is D then you would enter d setup exe Click OK to start the installer Installing Oracle Procedural Gateway Visual Workbench for WebSphere MQ Once you have the installer up and running you can proceed with the installation of Oracle Procedural Gateway Visual Workbench for WebSphere MQ The installer is essentially a wizard that presents a number of pages to you so that you can complete the installation of the Visual Workbench Installation 5 1 Removing the Software The first page that the installer presents is the Welcome page To continue with the installation click Next to display the File Locations
76. hen compile and test the MIP on the production server Figure 1 1 illustrates the PG4MO Visual Workbench s development and run time environments 1 2 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide The PG4MQ Visual Workbench Development Environment Figure 1 1 Development Environment Run Time Environment D Development Oracle Client Application Environment Message Interface Package MIP Visual Workbench NT PL SQL Code Oracle Production System Server Oracle Procedural Gateway Visual Workbench Repository IBM MQSeries ES D IBM MQSeries Application The PG4MQ Visual Workbench The Visual Workbench one for each developer simplifies the work needed to access message queuing applications through the gateway Use the Visual Workbench to create data profiles create message queue profiles create interface profiles generate a MIP Test the gateway and compile the MIP in the development environment Provide MIP templates to use as a starting point for development Introduction 1 3 The PG4MQ Visual Workbench Development Environment Compile the MIP and test it on the production system The MIP uses the profile information you provide enabling communication between an Oracle application and a non Oracle message queuing application Crea
77. his extension exists only in the Visual Workbench context and is not valid IBM VS COBOL II syntax The gateway administrator or application developer can use this extension to specify the criteria by which to apply the redefinition For example a record type field is often present in a record and different record formats apply depending on which record type is being processed The specification of which type values apply to which redefinition is typically contained in the application programming logic not in the data definition The WHEN criterion is included in data definitions to specify which conversion to perform on redefined formats in the data conversion package The Visual Workbench generates PL SQL nested record declarations which correspond in name and data type to the subordinate elements covered by the REDEFINED definition LEVEL 01 REDEFINE is ignored permitting remote host copybooks to include definitions which REDEFINE other transaction working storage buffers without having to define such buffers in the data conversion package or alter the copybook used as input for the definition 7 24 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide PL SQL Naming Algorithm SYNCHRONIZED and SYNCHRONIZED RIGHT SYNCHRONIZED and SYNCHRONIZED RIGHT cause the numeric field to be aligned on boundaries as dictated by the remote host environment compiler language and data type Numeric conversion is
78. ications of the data profile and sends the employee record to the specified queue using the passed in queue name priority and expiration taking the other options and properties from the message queue profile The message property priority nsgProps priority directs the ENQUEUE procedure to give the message a priority of 4 when enqueuing the message instead of using the value set by the message queue profile The expiration property msgProps expiration specifies that the message is to remain on the queue unless it is dequeued again DECLARE message EMPLOYEE EMPLOYEE Typ enqueueOpts PGM BOM ENQUEUE OPTIONS Typ msgProps PGM BOM MESSAGE PROPERTIES Typ msgid RAW 24 queueHandle PGM BOM QUEUE HANDLE Typ queueName VARCHAR2 48 CURSOR C1 IS SELECT EMP NO EMP FNAME FROM EMP BEGIN msgProps priority 4 msgProps expiration PGM BOM NEVER openOpts open mode PGM BOM ENQUEUE queueName emp HIRE QOPEN queueName openOpts queueHandle OPEN C1 LOOP FETCH C1 into message employeeno message firstname EXIT when C1 NOTFOUND HIRE ENQUEUE queueHandle queueName enqueueOpts msgProps 7 10 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide DEQUEUE Procedure message msgid ENDLOOCP CLOSE C1 HIRE QCLOSE queueHandle COMMIT EXCEPTION WHEN OTHERS THEN HIRE QCLOSE queueHandle ROLLBACK RAISE END DEQUEUE Procedure The DEQUEUE procedu
79. ile for the MIP Refer to the gateway installation guide for your platform for information about creating database links The PL SQL packages DBMS OUTPUT DBMS PIPEand UTL RAW must exist in the production Oracle server If they are not present then see your DBA about installing these packages a The PL SQL packages PGM PGM BOM PGM SUP and UTL PG are also required in the production Oracle server If they are not present then run the deployment script on the production Oracle server before compiling the MIP See Also Appendix A Preparing the Production Oracle Server for more information If any of these requirements are not met then the PG4MQ Visual Workbench reports an error when you try to compile Compiling the MIP for the Production Oracle Server When you compile the MIP for a production Oracle server the PG4MQ Visual Workbench reads the MIP code from the repository establishes a connection with the designated production Oracle server compiles the MIP and stores the compiled MIP as an object in the production Oracle server To compile the MIP for the production system server 1 Open the PG4MQ Visual Workbench repository Open the Interfaces folder Open the PL SQL folder Select the MIP to compile by clicking it oF O Click the Compile icon in the tool bar or the Compile button on a panel The Message Interface Package Compiler dialog box appears Compiling the MIP on page 6 26 shows this dialog
80. interface updates the interface profile and the generated MIP If the message queue profile you are updating is being used by an interface profile then a message prompts you to confirm the change Refer to Figure 6 11 Figure 6 11 Dialog Box to Confirm a Change amp x The Cobol data profile DEPOSIT is used in one or more interfaces updating this profile will cause these interfaces to be updated too Do you want to proceed OK Show Interfaces Removing a Message Queue Profile The following example shows how to remove a message queue profile from the repository 1 Open the PG4MQ Visual Workbench repository Open the Message Queue Profiles folder Open the WebSphere MQ folder Select the message queue profile to update by clicking it once Click the Remove icon in the tool bar on RYN A dialog box asks you to confirm the removal Refer to Figure 6 12 Figure 6 12 Dialog Box to Confirm Removal of a Profile E x Are you sure you wantto remove DEPOSIT from the repository i E Cancel Click OK to remove the message queue profile Removing a message queue profile that is associated with an interface removes the message queue profile both from that interface profile and from the generated MIP If the message queue profile you are removing is being used by an interface profile then a message prompts you to confirm the change Using the Oracle Procedural Gateway Visual Workbench for WebSphere
81. ipts 5 4 installing therepository 5 3 installing the PG4MQ Visual Workbench client 5 1 inter application messages 6 9 interface procedures fourina MIP 7 2 interface profile 6 2 before you compile the MIP 6 34 compatibility with beta release 6 17 creating 6 14 generating a MIP 7 1 HIRE 7 3 introduction 1 4 MIP toolarge 2 2 removing a message queue profile 6 14 updating a message queue profile 6 13 invalid parameter 7 7 IP address in the Host Name field 6 3 J JUSTIFIED 7 23 JUSTIFIED LEFT 7 23 JUSTIFIED RIGHT 7 23 K known problems 3 2 mapping to PL SQL 6 6 6 7 maximum open cursors 2 2 message interface package see MIP Message Interface Package Tester dialog box 6 33 message queue profile 6 2 creating 6 9 HR 7 3 introduction 1 4 removing 6 13 updating 6 13 UPS 6 12 message queue properties Acknowledgment 6 11 Delivery Mode 6 11 Dequeue Mode 6 12 Expiration 6 11 Message Type 6 11 Priority 6 11 Profile Name 6 10 Queue Name 6 10 Response Queue 6 11 Security ID 6 10 Visibility 6 11 6 12 WaitInterval 6 12 message queuing systems 1 1 Message Type parameter for message queue profile 6 11 message properties parameter 7 9 7 12 MESSAGE PROPERTIES Typ 7 16 MESSAGE PROPERTIES Typ type definition 7 16 message type parameter 7 19 MIP adding code to a template 6 30 compiling 6 26 contains ENQUEUE and DEQUEUE procedures 7 3 contents of 7 2 data profilein
82. itory Enter Y and press Enter for the prompt to remove public synonyms and development roles This returns the repository to private status You can exit the script now by entering N and pressing Enter or you can proceed to the next prompt under this step If you are certain that you want to remove the private repository then enter Y and press Enter The script removes all repository tables and related packages Installation 5 7 Installing the Visual Workbench Repository 5 8 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide 6 Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ This chapter describes how to use the Oracle Procedural Gateway Visual Workbench for WebSphere MQ to connect to a PG4MQ Visual Workbench repository create new data profiles update and remove data profiles associated with an interface profile create new message profiles update and remove a message profile associated with an interface profile create and update MIPs test the message queuing gateway associated with a MIP compile a MIP use the MIP templates to test the generated MIP and the data conversion package and prepare a MIP for production This chapter contains the following sections a Overview of PG4MQ Visual Workbench Development Starting the PG4MQ Visual Workbench and Connecting to a Repository Creating a Data Profile Updating a Data Profile Creating a Message Que
83. ity ID 6 10 security id 7 19 to control sending and receiving messages 7 3 user name 6 3 Visibility 6 11 6 12 visibility 7 15 Wait Interval 6 12 password parameter for connecting 6 3 payload parameter 7 8 7 11 7 12 PG4MOQ Visual Workbench components 1 2 development environment 3 1 development privileges for repository 5 6 installing the client 5 1 installing the repository 5 3 overview of development process 6 1 remove repository 5 7 repository 1 6 repository directory tree 6 4 starting 6 3 uses 1 4 window 6 4 PG4MQ Visual Workbench Wizard 6 2 6 14 PGM_BOM package 5 6 6 34 7 17 7 18 A 1 A 2 PGM SUP package 5 6 6 34 7 18 A 1 A 2 pgm8 sql A 2 PGMADMIN 5 6 5 7 6 3 pgmbqms8 sql script A 2 pgmdeploy9 sql script A 1 PGMDEV role 5 6 pgmsup8 sql script A 2 pgmundeploy9 sql script A 1 pgvwbremove sql script 5 7 pgvwbrepos sql script 5 5 5 6 PIC9 7 22 PICG 7 22 PIC X 7 21 PL SQL Code 6 18 code 6 28 code panel 6 28 data conversion package 7 19 incorrect precision of types 2 2 installing missing packages A 1 interface 6 14 mapping 6 6 6 7 MIP in a PL SQL program 6 28 naming algorithm 7 25 delimiters 7 25 duplicate names 7 26 qualified compound names 7 25 truncated and non unique names 7 26 package 1 5 2 2 6 34 7 1 7 20 A 1 A 2 A 3 packages required for data conversion 7 20 removing packages A 3 verifying packages exist A 1
84. l Workbench and may be abbreviated as PG4MQ Visual Workbench Related Documents Database administrators DBAs use this installation guide to create the Visual Workbench repository and install the Visual Workbench server They also use the Oracle Open Gateways Guide for SOL Based and Procedural Gateways You might also need Oracle server documentation or other related publications Some references that you might find helpful are Oracle Database Platform Guide for Microsoft Windows 32 Bit Oracle Database Administrator s Guide Oracle Database Application Developer s Guide Fundamentals Oracle Database Concepts a Oracle Database Error Messages a Oracle Database Net Services Administrator s Guide Conventions viii The following text conventions are used in this document Convention Meaning boldface Boldface type indicates graphical user interface elements associated with an action or terms defined in text or the glossary italic Italic type indicates book titles emphasis or placeholder variables for which you supply particular values monospace Monospace type indicates commands within a paragraph URLs code in examples text that appears on the screen or text that you enter 1 Introduction The Oracle Procedural Gateway Visual Workbench for WebSphere MQ is for developers who are writing Oracle applications that communicate with non Oracle message queuing applications and who are using an Or
85. ld in the open options structure is always a constant ENQUEUE when the queue is open for enqueueing The open mode field can take values REMOVE or BROWSE when the queue is open for dequeueing NOTES If the procedure fails an exception is raised RETURNS queue handle A structure containing the handle to the queue and the mode in which the queue is open ok 0o oot oot ook ook oot ot PROCEDURE QOPEN queue name IN VARCHAR2 open options IN PGM BQM OPEN OPTIONS Typ queue handle OUT PGM BQM QUEUE HANDLE Typ NAME Procedure QCLOSE Relinquish access to a previously open queue DESCRIPTION This procedure takes on input a queue handle structure and relinquishes the access to the queue After QCLOSE the handle becomes invalid and cannot be used for the enqueue or dequeue calls any more NOTES If the procedure fails an exception is raised RETURNS void PROCEDURE QCLOSE queue handle IN OUT PGM BOM QUEUE HANDLE Typ NAME Procedure ENQUEUE Put a EMPLOYEE message on the queue basic DESCRIPTION This procedure takes on input a payload parameter of PL SQL type EMPLOYEE EMPLOYEE Typ converts it to the 7 4 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide About the Message Interface Package MIP E 3 i E native format understood by the remote application and sends it to the queue defined for this interface Th
86. ling applications the data conversion package name the data profile name created in the Visual Workbench must be prefixed as the leftmost qualifier These examples show the fully qualified reference to the PL SQL variable BIRTHDAY is EMPLOYEE EMPLOYEE Typ BIRTHDAY BONUS is EMPLOYEE EMPLOYEE Typ BONUS Using the Generated MIP 7 25 PL SQL Naming Algorithm Truncated and Non Unique Names The Visual Workbench truncates field names and corresponding PL SQL variable names when the name exceeds a 26 bytes for fields within an aggregate record or group This is because each field or PL SQL variable name must have the suffix Typ for group names and Tb1 for element names with a repeating group 30 bytes because of a PL SQL limitation for any name The rightmost four characters are truncated imposing the restriction that names be unique to 26 characters Duplicate Names COBOL allows repetitive definition of the same group or element names within a record and the context of the higher level groups uniquely qualifies names However because data conversion packages generated by the Visual Workbench declare PL SQL record variables that reference nested PL SQL records for subordinate groups and fields such nested record types can have duplicate names Given this COBOL definition ZIP is uniquely qualified in COBOL but the corresponding PL SQL declaration would have a duplicate nested record type for ZIP The PL SQL declaration is
87. ls then it raises a PL SQL exception Exceptions can be raised for various reasons such as an error occurring during conversion or a problem reported by the message queuing system The application calling the procedure is responsible for handling the exception Using the Generated MIP 7 11 DEQUEUE Procedure Syntax PROCEDURE DEQUEUE payload OUT type definition The following table describes the parameters that need to be specified Parameter Description payload Returns the message data formatted according to the type definition NULL is an invalid value type definition See Also Type Definitions on page 7 14 and Data Conversion Package on page 7 19 Example In this example message contains an employee record defined by a data profile named EMPLOYEE It is passed as an output argument to the basic DEQUEUE procedure of a MIP named HIRE When it is called the MIP retrieves a message from the specified queue using the DEQUEUE options of the message queue profile After the message is retrieved it is converted from native to PL SQL format according to the specifications of the data profile and returned in the output parameter message In this example the application uses the dequeued message to obtain the employee s first name DECLARE firstName VARCHAR2 30 message EMPLOYEE EMPLOYEE Typ BEGIN HIRE DEQUEUE message firstName message FIRSTNAME COMMIT EXCEPTION WHEN OTHERS THEN ROLLBACK R
88. ls Help Qm 3 P ff yk 3 b J 7 Open Glose Creste Create As Update Remove Select Add Refresn Comple Test Wizard S REPOSITORIES 3 6 bug stadf34 H H DATA PROFILES 1 93 COBOL Eb EMPLOYEE Status E CREER Updated By PGMADMIN z Last Updated 07 jul 05 14 46 Eb PGCOMP T MESSAGE QUEUE PROFILES MaSeries Properties 3 MQ PROFILE1 es ys Queue Name QUEUE1 Security Id i G INTERFACES Les PL 8QL Enqueue Visibility On Commit Dequeue Visibility On Commit Dequeue Mode Remove Wait 2 Priority Default Expiration Never Message Type Response Queue QUEUE2 mete 12a EE eek Update Inserted MGSeries Profile UPS at the repository bug stadf34 Updating a Message Queue Profile The following example describes how to update a message queue profile 1 Open the PG4MQ Visual Workbench repository Open the Message Queue Profiles folder Open the WebSphere MQ folder Select the message queue profile to update by clicking it once Click the Update icon in the tool bar oe a F Ye yN The Message Queue Properties dialog box appears 6 12 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Removing a Message Queue Profile See Also Creating a Message Queue Profile on page 6 9 for details about this dialog box 7 Modify the properties as necessary then click Apply Updating a message queue profile that is associated with an
89. n on a panel The Message Interface Package Tester dialog box appears By default the PG4MQ Visual Workbench runs the test on the Oracle server where the repository resides To conduct the test on a different server select the Connection tab and modify the connection information On the General panel choose MIP DEQUEUE operation using template code as illustrated in Figure 6 31 6 30 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Preparing the MIP for Production Figure 6 31 Dialog Box for Message Interface Package Tester IS Message Interface Package Tester E Test Interface MQ EMPLOYEE g Select Test Operation C MIP ENQUEUE operation using template code C Gateway ENQUEUE operation C Gateway DEQUEUE operation Test results FEN Run Test Edit Template Close Revert Help 5 Click Run Test When the test completes the results appear in the Test Results window To see the test code for details of the test click Edit Code Note The DEQUEUE test reports success when it has retrieved a message from the queue All exceptions are reported in the test results panel A no messages available exception appears when an application tries to dequeue a message when there are no more messages on the queue The no messages available exception might result if the Wait Interval is set in the message queue profile and there are no more messages on the queue
90. ncy symbols and spaces These data types are supported COMPUTATIONAL binary COMPUTATIONAL 3 packed decimal 7 22 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide COBOL Format Conversion FILLER COMPUTATIONAL 4 binary a DISPLAY zoned decimal For DISPLAY data types these sign specifications are supported SEPARATE CHARACTER LEADING TRAILING COMPUTATIONAL 1 and COMPUTATIONAL 2 floating point data types are not supported The Visual Workbench recognizes COBOL FILLER fields by the spelling of the element name FILLER The Visual Workbench does not generate any data conversion for such elements but does require that their space be properly allocated to preserve offsets within the records exchanged with the remote host translation If a RENAMES or REDEFINES definition covers a FILLER element then the Visual Workbench generates data conversion statements for the same area when it is referenced as a component of the RENAMES or REDEFINES variable Such data conversion reflects only the format of the RENAMES or REDEFINES definition and not the bounds of the FILLER definition COBOL Format Conversion This section describes COBOL format conversion JUSTIFIED and JUSTIFIED RIGHT JUSTIFIED and JUSTIFIED RIGHT cause remote host transaction data to be converted as a PL SQL CHAR data type according to character data type for both IN and OUT parameters Input
91. nformation before rerunning the script Step 2 Check for existing Workbench Repository The script checks for an existing Visual Workbench repository and for the data dictionary If neither one is found the script proceeds to Step 3 If the data dictionary exists then the script stops Choose another Oracle integrating server and rerun the script starting at Step 1 Choose a repository server on page 5 4 If a Visual Workbench repository exists then the script gives you the following options A Upgrade the existing private repository to public status and proceed to Step 3 B Replace the existing repository with the new private repository and proceed to Step 3 C Stop the script Step 3 Check for required PL SQL packages The script checks for the existence of UTL_RAW DBMS_OUTPUT and DBMS_PIPE in the Oracle integrating server If this software exists then the script proceeds to Step 4 Installation 5 5 Installing the Visual Workbench Repository The script stops if this software does not exist Refer to your Oracle integrating server DBA about the missing software After the software is installed rerun the script Step 4 Install the UTL PG package The script checks for the existence of the UTL_PG package If it does not exist then the UTL PG package is installed The script proceeds to Step 5 If UTL PG exists then you are prompted to reinstall it Press Enter to reinstall UTL PG Step 5 Create the admin
92. ng 6 17 template 6 17 viewing the generated code 6 17 compatibility MIP and beta version MIP 2 1 6 17 compilation errors 6 27 compiling the MIP 6 26 Connect dialog box 6 3 Index connecting to a repository 6 3 copybook and data mapping information 6 7 COBOL 6 2 sample files 6 6 correlation parameter 7 19 creating a message queue profile 6 9 an interface profile and generating a MIP 6 14 data profiles 6 5 the administrative user 5 6 cursors maximum open 2 2 D data conversion COBOL 7 21 FILLER 7 23 package description 7 19 duplicate names of record types 7 26 EMPLOYEE 7 20 gateway test code 6 22 in run time environments 7 21 name prefixed as the leftmost qualifier 7 25 using MIP templates 6 28 using PL SQL RECORDs of TABLEs 7 24 warnings with JUSTIFIED LEFT 7 23 with WHEN criterion 7 24 PIC 9 7 22 PICG 7 22 PIC X 7 21 PIC Xexample 7 21 PL SQL mapping example 7 20 PL SQL naming algorithm 7 25 required PL SQL packages 7 20 Data Definition Name parameter for data profile 6 6 data dictionary checked by pgvwbrepos sql script 5 5 data mapping package 6 17 data profile COBOL 6 8 6 9 creating 6 5 Define COBOL Data Definition dialog box 6 5 definition 6 5 7 2 Index 1 EMPLOYEE 7 3 ENQUEUE and DEQUEUE procedures 7 3 generated data mapping 6 17 introduction 1 4 message exchange 6 2 PL SOL mapping 6 7 removing 6 9 updating 6 8 database link connecting MIP to a
93. ng application to the dequeuing application Applications are responsible for the usage of the specified response queue PGM BQM Package Exceptions The type definitions and constants described in Type Definitions on page 7 14 are defined in the PGM BOM package This package must be installed on the production Oracle server before you deploy MIPs See Also Preparing the MIP for Production on page 1 6 for more information Applications are responsible for handling these PL SQL exceptions When an application assigns an invalid value to a type definition field the MIP procedures raise this PL SQL exception INVALID BGM VALUE EXCEPTION PRAGMA EXCEPTION INIT INVALID BGM VALUE 20002 When an application tries to dequeue a message when there are no more messages on the queue the MIP procedures raise this exception NO MORE MESSAGES EXCEPTION PRAGMA EXCEPTION INIT NO MORE MESSAGES 20003 Using the Generated MIP 7 17 Using the MIP for WebSphere MQ Naming See Also Using the MIP for WebSphere MQ on page 7 18 for more information You must prefix the type definitions constants and exceptions with PGM BQM No schema name need be provided because the package has a public synonym Example DECLARE message EMPLOYEE EMPLOYEE Typ dequeueOpts PGM BOM DEQUEUE OPTIONS Typ msgProps PGM_BQM MESSAGE PROPERTIES Typ msgid RAW 24 firstName VARCHAR2 30 BEGIN dequeueOpts DEQUEU
94. nne nennen 6 13 Removing a Message Queue Profile ener e nennen 6 13 Creating an Interface Profile and Generating a MIP ou ccc eee cs eee eee 6 14 Alternative method for starting the Wizard to create an interface 6 16 Viewing the Generated Code itti e ee eds 6 17 Updating MID ere reote eot e fee nee eee ere e eee ee ri e nee RE etes 6 19 Alternative method for starting the Wizard to update an interface ssssuss 6 21 Testing the Gateway steer dete petro e ient HH eb Fe ege inan 6 22 Conipiling the MID eiie to iem eee tae ae Ho Or etnies kon ih erri eie 6 26 Using the MIP Templates ee eet e dien ese e Ped erige E Re 6 28 Completing the Templates nire ae tee te eese Peta ie Pec ie e breed i o ede eros 6 30 Using a Template to Test the MIP sees nene nennen nennen nennen 6 31 Testing the ENQUEUE Procedure Template sss eene 6 32 Testing the DEQUEUE Procedure Template sess eee 6 33 Preparing the MIP for Productionsin einasi siinset nnne n nennen nennen 6 34 Before You Compile the MIP on the Production Oracle Server sess 6 34 Compiling the MIP for the Production Oracle Server sess 6 35 Granting Execution Privileges to a MIP sssesseeeeee eee enne 6 35 Testing the MIP on a Production System sse nennen nennen 6 36 Using the Generated MIP About the Message Interface Package MIP
95. nstallation and User s Guide Installing the Visual Workbench Repository If the DESCRIBE statement is successful then it indicates that caths sq1 has been run for the database server Otherwise you must run the caths sq1 script first Step 6 Create a database link Create a database link on your Oracle Production System Server to access the Oracle Procedural Gateway for WebSphere MQ If you do not already have a database link then refer to the gateway installation guide for your platform for information about creating database links Visual Workbench Repository Installation Tasks Use pgvwbrepos sql to install the Visual Workbench Repository on the current release To run pgvwbrepos sql ensure that you are currently in the ORACLE HOMENpg4mq admin Nrepo directory and then enter sqlplus nolog pgvwbrepos sql Note If you are installing the Visual Workbench Repository on Oracle8i or earlier then you need to use pgvwbrepos8 sql All the examples in this section are provided with the assumption that you are installing on the current release The script takes you through the following steps Step 1 Enter the database connection information Use the default of LOCAL by pressing Enter Next you are prompted to enter the passwords for the SYSTEM and SYS accounts of the Oracle integrating server Press Enter after entering each password The script stops if any of the information is incorrect Verify the i
96. on This queue name is forwarded by the message queuing system as is from the enqueuing application to the dequeuing application The applications are responsible for usage of the specified response queue Response Queue is case sensitive and can be up to 48 characters long A Response Queue must be specified if the Acknowledgment parameter is set to either Positive or Negative 6 10 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Creating a Message Queue Profile Message Queue Definitions Message Type Priority Expiration Acknowledgment Delivery Mode Enqueue Option Visibility Dequeue Option Wait Interval Visibility Specifies an application supplied free format description of the message forwarded by the message queuing system Message Type indicators can be up to 8 characters long Specifies the priority of the message for an ENQUEUE operation Refer to the documentation for your message queuing system for a definition of priority because definitions vary according to the message queuing system you are using For the WebSphere MQ product the minimum value is 0 and the maximum value is 9 Default is the default for the queue as specified by the message queuing system Specifies when the message expires determining in seconds how long the message is available for dequeuing Never specifies that the message does not expire and is available on the queue for an
97. on 2 1 Changes and Enhancements Release 10 2 0 1 0 The Visual Workbench repository is not compatible with the repository released with the Visual Workbench releases 9 0 1 and earlier You must re create interface profiles that were created and stored in the repository using releases 9 0 1 and earlier Changes and Enhancements Release 10 2 0 1 0 There is only one enhancement in this release Accessing release 10 2 0 1 0 and all earlier releases of PG4MQ Visual Workbench Repositories The current release of the Oracle Procedural Gateway Visual Workbench for WebSphere MQ is enhanced to be able to access release 10 2 0 1 0 and all earlier releases of PG4MQ Visual Workbench repositories Known Problems The description of problems includes suggestions for dealing with them when possible If you have questions or concerns about the problems then contact Oracle Support Services A current list of problems is available online Contact your local Oracle office for information about accessing this list Maximum Open Cursors This error might appear during a PG4MQ Visual Workbench operation ORA 01000 Maximum open cursors exceeded Most Visual Workbench operations do not work correctly after this error and the error continues to appear To resolve this problem set the CLOSE CACHED OPEN CURSOR initialization parameter to TRUE for the Oracle server where the Visual Workbench repository resides See Also Related Documents on page
98. or starting the Wizard to update an interface on page 6 21 for a description of another way to start the Wizard 6 18 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Updating a MIP Figure 6 18 Wizard Welcome Window S Wizard Welcome Lx Welcome to the Wizard This wizard guides you through creating or updating an interface that contains code to simplify access to different Message Queuing middleware products from your Oracle application Click Next to begin creating or updating an interface T Click here to skip this message next time Cancel Help lt Back Next Finish Click Next to see the next Wizard window and select Update Existing Interface if you previously did not enable the Welcome window then this is the first window you see Refer to Figure 6 19 Figure 6 19 Wizard Interface Window STORT Wizard Lx Which action can help you with Choose an action then click Next C Create New Interface amp Update Existing Interface Cancel Help lt Back Next Finish Click Next to begin the Wizard s step by step instructions to Select the repository programming language and MIP to update a Specify another database link if necessary a Create a new data profile or update the selected data profile as necessary a Create a new message queue profile or update the selected message queue profile Using the Oracle Proced
99. ou can compile MIPs on that server This appendix describes how to run scripts pgmdeploy sql and pgmundeploy sq1 and how to later remove any PL SQL packages that are not needed on your system The following topics are included Introduction Introduction Verifying and Installing PL SQL Packages Removing the PL SQL Packages Before you can compile MIPs on a production Oracle server the following PL SQL packages must be present on the production Oracle server DBMS PIPE DBMS OUTPUT and UTL RAW These packages are shipped with each Oracle server and are usually already installed PGM PGM BQM PGM SUP and UTL PG These packages are shipped with your Oracle Procedural Gateway for message queuing They are installed during the creation process of the Visual Workbench repository Do not run deployment script on the Oracle server with an installed Visual Workbench repository If the Oracle server used for the repository is different than the Oracle server used in the production environment then you must install these packages on the production Oracle server This section describes how to run pgmdeploy sql a deployment script to verify the existence of the required PL SQL packages and to install some of them if they do not exist on the production Oracle server pgmundeploy sql a script to remove the PL SQL packages from a production Oracle server Preparing the Production Oracle Server A 1 Verifying and Installing PL SQL Packages
100. panel by clicking its name In the example shown in Figure 6 27 the ENQUEUE DEPOSIT template is selected for the MIP named DO DEPOSIT 6 26 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Using the MIP Templates Figure 6 27 ENQUEUE DEPOSIT template for MIP named DO DEPOSIT J REPOSITORIES bug stadf34 CH DATA PROFILES 2 53 COBOL DEPOSIT EMPLOYEE EMPMAP ONELINE PGCOMP 3 MESSAGE QUEUE PROFILES oa IBM MOSERIES te DEPOSIT_QUEUE 3 MQ_PROFILE1 3 UPS GR INTERFACES ft PUSQL ik weeny E DEPOSIT QUEUE 4 DEPOSIT Jam ENQUEUE DEPOSIT DEQUEUE DEPOSIT DO DEPOSIT DEPOSIT MIP Template MIP Template Message Interface Package Cobol Mapping 07 jul 05 14 49 07 jul 05 14 48 To see the template code click View Code Figure 6 28 shows the sample ENQUEUE DEPOSIT template code for the MIP named DO DEPOSIT Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 27 Using the MIP Templates Figure 6 28 Code Viewer with Sample ENQUEUE DEPOSIT Template Code f 4 Code Viewer lolx File Edit View Help a E Exaoeceoerosr ris Print Ct Copy Paste MIP Template ENQUEUE DEPOSIT v DECLARE message DEPOSIT DEPOSIT_Typ h0bj PGM BQM QUEUE HANDLE Typ openOpts PGM BQM OPEN OPTIONS Typ enqueueOpts PGM BQM ENQUEUE OPTIONS Typ messageOpts PGM BQM MESSAGE PROPERTIES Typ queueName VARCHAR2 48 NULL m
101. parameter for connecting 6 4 ORCL default SID 6 4 OUT parameter 7 23 7 25 output parameter 7 8 7 11 7 12 7 13 7 16 P package data conversion 6 22 6 28 7 19 7 20 7 21 7 23 7 24 7 25 7 26 data mapping 6 17 DBMS OUTPUT 5 4 5 6 6 34 A 1 A 2 DBMS PIPE 5 6 6 34 A 1 A 2 generated 6 27 generated code 6 17 PGM BOM 5 6 6 34 7 17 7 18 A 1 A 2 PGM SUP 5 6 6 34 7 18 A 1 A2 PL SQL 1 5 2 2 6 24 7 1 7 20 A 1 A 2 A 3 test code 6 36 UTL PG 5 6 6 34 A 1 A 2 UTL RAW 5 4 5 6 6 34 A 1 A 2 panel General 6 4 parameter Acknowledgment 6 11 COBOL copybook file 6 6 correlation 7 19 Data Definition Name 6 6 datalength 7 24 Delivery Mode 6 11 Dequeue Mode 6 12 dequeue options 7 12 7 15 enqueue operations 7 9 enqueue options 7 15 Expiration 6 11 hostname 6 3 if NULL 7 9 7 12 IN 7 23 7 25 initialization CLOSE_CACHED_OPEN_ CURSOR 2 2 input 7 7 7 9 7 10 7 12 7 13 7 16 invalid 7 7 Index 4 Message Type 6 11 message properties 7 9 7 12 message_type 7 19 MIP restrictions a table 7 19 msgid 7 9 7 12 7 19 national language support 6 6 open_options 7 14 openOpts open mode 7 10 Oracle SID 6 4 OUT 7 23 7 25 output 7 8 7 11 7 12 7 13 7 16 password 6 3 payload 7 8 7 11 7 12 port number 6 3 Priority 6 11 priority 7 19 Profile Name 6 10 Queue Name 6 10 queue name 7 9 7 12 7 19 Response Queue 6 11 response queue 7 19 Secur
102. port number default 6 3 parameter for connecting 6 3 priority parameter 7 19 priority parameter for message queue profile 6 11 private access privileges 5 6 private repository 5 7 privileges private access 5 6 privileges public access 5 6 problems incorrect precision of PL SQL types 2 2 procedural gateway database link for access 5 5 procedure DEQUEUE 60 28 6 30 7 2 7 3 ENQUEUE 6 28 6 30 7 2 7 3 7 8 ENQUEUE advanced 7 9 QCLOSE 7 2 7 7 QOPEN 7 2 7 7 production introduction to preparing the MIP for production 1 6 preparing the MIP for production 6 34 profile data for message exchange 6 2 HR 7 3 interface 6 2 message queue 6 2 Profile Name parameter for message queue profile 6 10 program file transfer 5 4 prvtpg sqlscript A 2 prvtrawb plb script 5 4 public access privileges 5 6 Q QCLOSE close an open queue 7 2 procedure 7 2 7 7 syntax 7 7 QOPEN open a specified queue 7 2 procedure 7 2 7 7 syntax 7 7 qualified compound names in data conversion 7 25 Queue Name parameter for message queue profile 6 10 queue name parameter 7 9 7 12 7 19 R remove the PG4MQ Visual Workbench repository 5 7 removing a data profile 6 9 removing a message queue profile 6 13 RENAMES clause 7 24 repository and compiling 6 27 choosing a server 5 4 connecting to 6 3 development privileges 5 6 directory tree PG4MQ Visual Workbench 6 4 installation scripts 5 4 installing
103. procedural gateway 7 2 data mapping code invoked by MIP 7 2 DBMS OUTPUT package 5 4 5 6 6 34 7 20 A 1 A 2 DBMS PIPE package 5 6 6 34 A 1 A 2 default character set NLS 6 6 gateway test 6 22 port number 6 3 SID 6 4 Define COBOL Data Definition dialog box 6 5 6 8 delimiters in data conversion 7 25 Delivery Mode parameter for message queue profile 6 11 DEQUEUE advanced procedure 7 12 and no messages available exception 6 34 basic procedure 7 11 data conversion package and 7 19 DEQUEUE_OPTIONS_Typ 7 15 inthe MIP 7 11 MESSAGE PROPERTIES Typ 7 16 mode 6 12 naming and name resolution 7 11 procedure 6 28 6 30 7 2 7 3 template 6 30 test 6 34 testing the procedure template 6 33 through the gateway 6 22 Visibility parameter 6 12 Wait Interval parameter 6 12 Dequeue Mode parameter for message queue profile 6 12 dequeue options parameter 7 12 7 15 DEQUEUE OPTIONS Typ type definition 7 15 DESCRIBE statement 5 4 development overview of process 6 1 dialog box Connect 6 3 Define COBOL Data Definition 6 5 6 8 Error Messages 6 27 Message Interface Package Compiler 6 26 6 35 Message Interface Package Tester 6 22 6 32 6 33 WebSphere MQ Properties 6 10 directory script file 5 4 DO DEPOSIT ENQUEUE DEPOSIT example 6 28 MIP example 6 17 6 18 duplicate names in data conversion 7 26 Index 2 E EMPLOYEE data profile 7 3 ENQUEUE advanced procedure 7 9 basic procedure 7 8 d
104. pvd bane 7 13 Example RE 7 13 Type Definittons eon RB t ot ee er TEN ath ce 7 14 OREN OPTIONS Dy pian sei iiit ra ee E aoea tincerep A E rece Rea Een eR EEA oee RS S EAE Srani 7 14 DYLAN d 7 14 ENOUEUE OPTIONS Typ te ere eene iere p e e eR ete te oe 7 15 DEQUEUE OPTIONS Dy pienie sieni E EE AN R E 7 15 Synta eos sc S Aei tme red ied tied ed vA ome eor cA a E E 7 15 MESSAGE PROPERTIES Typ edet ea t meet pee ad ent ere deeds 7 16 SVILAXneubsisesvoiiseduhityatei bita b eiiis te Nerii rose te eter ea brevi a vie Et iei et 7 16 PGMEBOM Package eere Ee ee e eR Re pds e at re e meme ee intet peas 7 17 EXceptlons cited turen tas ie nd Heredia e hls ato t rise ut roe ra eU e EHE RR shea gh 7 17 INAMA E IRR EE RI ETRAS 7 18 Using the MIP for WebSphere MQ sess eene n enin nennen nennen 7 18 PGM SUP Package cti teet eR SER NER der te e e ois eh E ede dene e 7 18 EXGepOnDSs mette sedis cs nma egaedondee ten dandi qud Hc ade ast one hapten T TST 7 18 MIP RESC RONS ee tern tt a ti Visa el e E d de es 7 19 Data Conversion Package ssssssssssseeeeeeeeeeenenene nennen nnne nennen tenerent E 7 19 COBOL Data Type Conversion sene eene eene en nenne nnne nnne nnne 7 21 Ideo C ans 7 21 luec 7 22 PIC e
105. re Retrieves a message from a queue at a non Oracle message queuing system according to the specifications of a message queue profile or the input arguments of the procedure Converts the retrieved message contents of payload from native to PL SQL format according to the specifications of a data profile DEQUEUE Naming and Name Resolution Because the DEQUEUE procedures are part of a MIP applications must specify this fully qualified name when calling the procedure Schema name mip name DEQUEUE The following table describes the parameters that need to be specified Parameter Description schema name Name of the Oracle user that compiled the MIP mip name Name you specified when you created the MIP PL SQL runs the correct DEQUEUE procedure based on the parameters provided upon invocation For example if only a payload output parameter is specified the basic DEQUEUE procedure is run When more than one data profile is added to a MIP and multiple versions of basic and advanced DEQUEUE procedures are generated for the MIP the type of payload used determines which DEQUEUE procedure in the MIP is selected Basic DEQUEUE Procedure The basic DEQUEUE procedure retrieves the message from a queue according to the specifications of the message queue profile converts the message according to the specifications of the data profile and returns the result in the payload output parameter If the basic DEQUEUE procedure fai
106. re information about GRANT Testing the MIP on a Production System After you have successfully compiled the MIP on a production Oracle server you can test it there The PG4MQ Visual Workbench reads the test code package from the PG4MOQ Visual Workbench repository and runs it on the production system See Also Iesting the Gateway on page 6 22 for information about navigating to the Message Interface Package Tester dialog box Modify the connection information in the Message Interface Package Tester dialog box to specify the production Oracle server for the test instead of using the default Oracle server where the repository resides See Also Using a Template to Test the MIP on page 6 31 for instructions on running the test Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 33 Preparing the MIP for Production 6 34 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide 7 Using the Generated MIP This chapter describes the contents of a MIP specification the MIP procedures examples of how to use a MIP in an Oracle application the contents and use of the data conversion package This chapter contains the following sections About the Message Interface Package MID MIP Procedures QOPEN Procedure OCLOSE Procedure ENQUEUE Procedure DEQUEUE Procedure Type Definitions MESSAGE PROPERTIES Typ PGM_BOM Package Using the MIP
107. red to in this guide as Visual Workbench is a development tool that simplifies integrating Oracle applications with non Oracle message queuing applications It is used by developers who write Oracle applications that communicate with non Oracle messaging and queuing applications using a procedural gateway for a message queuing system Procedural gateways for message queuing enable Oracle applications to send and retrieve messages from message queuing systems For more information see the gateway installation guide for your platform The Oracle 10g release of the Oracle Procedural Gateway for WebSphere MQ provides access to WebSphere MQ services Read this guide if you are responsible for tasks such as a Administering the gateway Setting up gateway security a Using the gateway Diagnosing gateway errors You must understand the fundamentals of your operating system the procedural gateways PL SQL the Oracle server and MOSeries software before using this guide to install configure or administer the gateway This document is intended for application developers who install and use the Visual Workbench on their workstations Documentation Accessibility Our goal is to make Oracle products services and supporting documentation accessible with good usability to the disabled community To that end our documentation includes features that make information available to users of assistive technology This documentation is avail
108. removal Refer to Figure 6 8 Figure 6 8 Dialog Box to Remove a Data Profile Are you sure you want to remove EMPLOYEE from the repository i Cancel 7 Click OK to remove the data profile Removing a data profile that is associated with an interface removes the data profile both from that interface profile and from the generated MIP Before removing the data profile the PG4MQ Visual Workbench prompts you to confirm the removal Creating a Message Queue Profile After connecting to a PG4MQ Visual Workbench repository and creating one or more data profiles create a message queue profile The message queue profile specifies how and where inter application messages are to be sent and retrieved The PG4MQ Visual Workbench maintains these definitions in the PG4MQ Visual Workbench repository at an Oracle server where the message queuing system accesses it using a message queuing gateway For example to create a message queue profile for a WebSphere MQ system Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 9 Creating a Message Queue Profile S MOSeries Properties Open the PG4MQ Visual Workbench repository by double clicking the repository icon or by clicking the plus sign next to the repository icon Open the Message Queue Profiles folder Select the WebSphere MO folder by clicking it once Click the Create icon in the tool bar The MQSeries Properties dialog box appears Refer to Figure 6 9
109. rface Package Tester S Message Interface Package Tester x Test Interface DO_DEPOSIT JU _ Select Test Operation C MIP ENQUEUE operation using template code C MIP DEQUEUE operation using template code C Gateway ENQUEUE operation Gateway DEQUEUE operation Test results Testing gateway DEQUEUE operation jTest ran successful JFinished test run Run Test Stop Run Edit Template 7 Revert Help 10 Leave Gateway DEQUEUE operation set 11 Click Run Test again to test that the last DEQUEUE operation retrieved the message The Message Interface Package Tester shows the result of the test If the only message on the queue is the one you sent in step 7 then the DEQUEUE operation fails because that message had already been retrieved Refer to Figure 6 24 Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 23 Compiling the MIP Figure 6 24 Dialog Box for Message Interface Package Tester E Message Interface Package Tester x Test Interface DO DEPOSIT JU Select Test Operation C MIP ENQUEUE operation using template code C MIP DEQUEUE operation using template code C Gateway ENQUEUE operation Gateway DEQUEUE operation Test results esting gateway DEQUEUE operation ORA 08501 No message available on the queue ORA 06512 at line 31 Edit Template Compiling the MIP After testing the message queue profile against the gateway compile the MIP A
110. rocedural Gateway Visual Workbenc F me 3 Code Viewer File Edit View Help 822 223 Save Export Print Cut Copy Paste Copyright c 2005 Oracle Corporation All rights reserved Message Interface Package specification DO_DEPOSIT for WebSphere MQ Using Database Link PG4MQDEPDBLINK REGRESS RDBMS DEV US ORACLE COM Using Message Queue Profile DEPOSIT QUEUE Queue QUEUEl Security ID Enqueue Visibility Dequeue Visibility Dequeue Mode Wait Correlation Priority Expiration Message Type Response Queue Delivery Mode Acknowledgement On Commit n Commit Remove 3 Default Never QUEUEZ Not Persistent None ow ow m y g ow wm m uon w Using Data Profile s DEPOSIT MIP procedures Procedure QOPEN Establish access to a queue Procedure QCLOSE Relinquish access to a previously opened queue Procedure ENQUEUE Put a DEPOSIT message on the queue basic Updating a MIP You might need to update a MIP if for example you need to add or remove data profiles or change the message queue profile To update a MIP start the PG4MQ Visual Workbench and connect to a repository See Also Starting the PG4MQ Visual Workbench and Connecting to a Repository on page 6 3 for more information You can start the Wizard by clicking the Wizard icon in the PG4MQ Visual Workbench tool bar The Welcome window appears Refer to Figure 6 18 See Also Alternative method f
111. rofile Syntax TYPE ENQUEUE OPTIONS Typ IS RECORD visibility binary integer default null where visibility specifies the transaction behavior of the ENQUEUE requests Choose one of the these modes a ON COMMIT Specifies that the ENQUEUE is part of the current transaction The operation is completed when the transaction is committed ON_COMMIT is the default as IMMEDIATE Specifies that the ENQUEUE is not part of the current transaction The operation constitutes a transaction of its own DEQUEUE_OPTIONS Typ This type defines the DEQUEUE options that can be specified by the calling application The structure type is used for the dequeue options parameter of the advanced DEQUEUE procedure If the fields of this parameter contain values then they override the settings of the message queue profile Syntax TYPE DEQUEUE OPTIONS Typ IS RECORD dequeue mode binary integer default null visibility binary integer default null wait binary integer default null msgid raw 24 default null correlation varchar2 128 default null The following table describes the parameters that need to be specified Parameter Description dequeue mode Specifies how messages are read from the queue REMOVE reads the message and removes it from the queue REMOVE is the default BROWSE reads a message from the queue but does not remove it visibility Specifies the transaction behavior of the DEQUEUE request ON COMMIT specifies tha
112. sgid RAW 24 BEGIN ft Open the queue open pts open mode PGM BQM ENQUEUE DO_DEPOSIT QOPEN queueName openOpts h0bj Populate message here Put the message on the queue DO_DEPOSIT ENQUEVE h0bj enqueueOpts messageOpts message msgid DO_DEPOSIT QCLOSE h0b3 p PT gt Before you use the MIP template code you must first 1 Compile the MIP on the Oracle server where the template code will be run See Also Compiling the MIP on page 6 26 for more information 2 Add code to the template See Also Completing the Templates on page 6 30 Completing the Templates Before testing an ENQUEUE template complete the template code by filling in the message variable fields Testing the ENQUEUE template fails if the fields of the message variable are not set before calling the ENQUEUE procedure A DEQUEUE template can run if you do not modify it but you usually add code to process the retrieved message after the DEQUEUE procedure is called To add code to the sample 1 Open the PG4MQ Visual Workbench repository 2 Openthe Interfaces folder 3 Open the PL SQL folder 6 28 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Using the MIP Templates 4 Select a MIP by clicking it 5 Select the PL SOL Code tab 6 Select a MIP template from the list and click View Code The Code Viewer
113. shown in Generated PL SQL on page 7 26 Example 7 1 COBOL definition 01 EMPREC 05 HIREDATE PIC X 8 05 BIRTHDATE PIC X 8 05 SKILL PIC X 12 O0CCURS 4 05 EMPNO PIC 9 4 05 EMPNAME 10 FIRST NAME PIC X 10 10 LAST NAME PIC X 15 05 HOME ADDRESS 10 STREET PIC X 20 10 CITY PIC X 15 10 STATE PIC XX 10 ZIP 15 FIRST FIVE PIC X 5 15 LAST FOUR PIC X 4 05 DEPT PIC X 45 05 OFFICE ADDRESS 10 STREET PIC X 20 10 CITY PIC X 15 10 STATE PIC XX 10 ZIP 15 FIRST FIVE PIC X 5 15 LAST FOUR PIC X 4 05 JOBTITLE PIC X 20 Generated PL SQL The Visual Workbench avoids declaring duplicate nested record types and generates this PL SQL based on the COBOL definition in COBOL definition on page 7 26 SKILL Key BINARY INTEGER TYPE SKILL Tbl is TABLE of CHAR 12 INDEX by BINARY INTEGER 7 26 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide PL SQL Naming Algorithm TYPE EMPNAME Typ is RECORD FIRST NAME CHAR 10 LAST NAME CHAR 15 TYPE ZIP Typ is RECORD FIRST FIVE CHAR 5 LAST FOUR CHAR 4 TYPE HOME ADDRESS Typ is RECORD STREET CHAR 20 CITY CHAR 15 STATE CHAR 2 ZIP ZIP Typ TYPE OFFICE ADDRESS Typ is RECORD STREET CHAR 20 CITY CHAR 15 STATE CHAR 2 ZIP ZIP Typ TYPE EMPREC Typ is RECORD HIREDATE CHAR 8 BIRTHDATE CHAR 8 SKILL SKILL Tbl EMPNO NUMBER 4 0 EMPNAME EMPNA
114. t When you get to Step 2 of the script enter A at the prompt to upgrade the private repository to public status a Use SQL Plus to connect to the repository as user PGMADMIN and grant the PGMDEV role to each user For example SQL gt GRANT PGMDEV TO SCOTT Remove the Visual Workbench Repository To remove a Visual Workbench repository on Oracle9i use the repository script pgvwbremove sql To run this script ensure that you are currently under the Oracle integrating server directory ORACLE HOMENpg4mq adminNrepo where you copied the scripts and then enter sqlplus nolog pgvwbremove sql 5 6 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Installing the Visual Workbench Repository Note If you are removing the Visual Workbench Repository on Oracle8i or earlier then you need to use pgvwbremove8 sql All the examples in this section are provided with the assumption that you are installing on the current release The script takes you through the following steps Step 1 Enter the database connection information Use the default of LOCAL by pressing Enter Next you are prompted to enter the passwords for the SYSTEM SYS and PGMADMIN accounts of the Oracle integrating server Press Enter after entering each password The script stops if any of the information is incorrect Verify the information before rerunning the script Step 2 Check for existing Workbench repos
115. t the ENQUEUE is part of the current transaction The visibility parameter is ignored when DEQUEUE MODE is set to BROWSE The operation is completed when the transaction commits ON COMMIT is the default I MMEDIATE specifies that the DEQUEUE is not part of the transaction The operation constitutes a transaction of its own Using the Generated MIP 7 15 MESSAGE PROPERTIES Typ Parameter Description wait Specifies the wait time if no message is available on the queue FOREVER specifies an unlimited wait time This is the default NO WAIT specifies that there is no wait time nnspecifies the time to wait in seconds msgid Specifies the message identifier of the message to be dequeued correlation Specifies the correlation identifier of the message to be dequeued MESSAGE_PROPERTIES_Typ This type defines or obtains the message options for a message by the calling application The structure type is used for the message_properties input parameter of the advanced ENQUEUE procedure and is an output parameter for the DEQUEUE procedure If the fields of this parameter contain values on input for the advanced ENQUEUE procedure then they override the settings of the message queue profile On output for the advanced DEQUEUE procedure the fields reflect the message properties given to the message either by the enqueuing application or the message queuing system Syntax TYPE MESSAGE PROPERTIES Typ is record priority
116. tent products and services from third parties Oracle is not responsible for the availability of or any content provided on third party Web sites You bear all risks associated with the use of such content If you choose to purchase any products or services from a third party the relationship is directly between you and the third party Oracle is not responsible for a the quality of third party products or services or b fulfilling any of the terms of the agreement with the third party including delivery of products or services and warranty obligations related to purchased products or services Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party Contents Introduction Message Queuing Systems sss eee enne enne e nenne nnne enne nennen nne 1 1 The Oracle Procedural Gateway for Message Queuing Systems sess 1 1 The PG4MOQ Visual Workbench Development Environment 7 T cesses 1 2 The PGAMO Vistial Workbench eate ete etae ettet tei Lie ELE etg 1 4 Creating Data Profiles ai he Dh eo ecd o o enti ettet 1 4 Creating Message Queue Profiles sse eee nennen nnne 1 4 Creating Interface Profiles and Generating the MIP sse 1 4 Testing tlie Gateway asirai eee nte tereti i ee i ee P ee Son e e doe reet 1 5 Compiling tlie MIP tette ERREUR E a vit 1 5 Using the MIP Templates 2 2 reete t He p iie e a
117. ter the connect string for the Oracle server LOCAL press Return to use the default of LOCAL At the script prompt Enter the following required Oracle server password enter the password of the SYS account After the script verifies the SYS account password it connects to the production Oracle server The script verifies and reports on which PL SQL packages are installed there If any of the Oracle server packages DBMS_OUTPUT DBMS_PIPE or UTL_RAWare missing the script stops Have your DBA install the missing packages and re run the deployment script If any of the Oracle packages PGM PGM BOM PGM_SUP and UTL PG are missing the script installs them on the production Oracle server A 2 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Removing the PL SQL Packages Removing the PL SQL Packages You can remove the PL SQL packages that were installed by the pgmdeploy sql script if for example none of your applications in the production environment uses a MIP To remove these packages perform the following steps 1 On your production Oracle server computer change directory to the directory containing the deployment scripts by entering the following command gt cd ORACLE HOME pg4mq admin deploy Run the script by entering sqlplus nolog pgmundeploy sql Atthescriptprompt Enter the connect string for the Oracle server LOCAL press Enter to use the default of LOCAL At
118. the script prompt Enter the required Oracle server passwords enter the password of the SYS account After the script verifies the SYS account password it connects to the production Oracle server and removes the packages installed by the pgmdeploy sq1 script After the pgmundep1oy sql script completes successfully applications on the production Oracle server fail if they attempt to reference any of the MIPs that are compiled there Preparing the Production Oracle Server A 3 Removing the PL SQL Packages A 4 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide A Acknowledgment parameter for message queue profile 6 11 with Response Queue 6 11 administrative user creating 5 6 C character sets in data profiles 6 6 choosing a repository server 5 4 CLOSE CACHED OPEN CURSOR initialization parameter 2 2 COBOL copybook 6 2 copybook file parameter 6 6 data definition 6 6 data profile 6 8 6 9 data profile example 6 5 data type conversion 7 21 FILLER data type conversion 7 23 format conversion 7 23 JUSTIFIED 7 23 JUSTIFIED LEFT 7 23 JUSTIFIED RIGHT 7 23 messages 6 2 OCCURS clause 7 24 PIC 9 data type conversion 7 22 PIC G data type conversion 7 22 PIC X data type conversion 7 21 RENAMES clause 7 24 supported version 1 4 SYNCHRONIZED 7 25 SYNCHRONIZED LEFT 7 25 SYNCHRONIZED RIGHT 7 25 version parameter for data profile 6 6 code data mappi
119. the templates and test them from the PG4MQ Visual Workbench The templates can be used as a starting point for your application to use the MIP for sending and retrieving messages See Also Creating an Interface Profile and Generating a MIP on page 6 14 and About the Message Interface Package MIP on page 7 1 for more information For more complete MIP information refer to Chapter 7 Using the Generated MIP Preparing the MIP for Production After completing development you Introduction 1 5 The PG4MQ Visual Workbench Development Environment Deploy the MIP on one or more production system servers where you plan to run your applications that use the MIP Compile the MIP on the production system Test the MIP on the production system See Also Preparing the MIP for Production on page 6 34 for more information The PG4MQ Visual Workbench Repository The Visual Workbench repository stores all data profiles message queue profiles interface profiles and MIPs The repository resides in an Oracle server and can be created in any Oracle server in the network The Oracle server used for the repository need not be the same Oracle server that is used in the production system by the applications referencing the MIPs it can be a different Oracle server 1 6 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide 2 Product Set Release Information Th
120. ting Data Profiles The gateway and a message queuing system are the transport mechanisms for message data They provide no data conversion capability This means that usually you must supply code to convert data between Oracle data types and non Oracle data types Using the Visual Workbench you create data profiles that when associated with an interface profile automatically add the necessary conversion code before a message is sent or after a message has been retrieved The Visual Workbench maintains the data profiles in the Visual Workbench repository To have the interface convert a COBOL structured message to PL SQL you create a COBOL data profile defining data conversion definitions The Visual Workbench generates PL SQL code to map between the COBOL data conversion definitions and those of PL SQL Currently only COBOL version IBM VS COBOLII is supported See Also Creating a Data Profile on page 6 5 for details Creating Message Queue Profiles Using the Visual Workbench you define the attributes of the message queues used to communicate between Oracle and non Oracle applications A message queue profile specifies how and where inter application messages are sent to the message queuing system and are retrieved from it When a message queue profile is associated with an interface profile the Visual Workbench generates the PL SQL code necessary to access the message queuing gateway and stores it in the Visual Workben
121. tion of its own Defines the time in seconds for a DEQUEUE operation to wait if no message is available on the queue For the WebSphere MQ product the minimum value is 0 and the maximum value is 231 1 Forever specifies that a DEQUEUE operation waits an unlimited time Forever is the default No Wait specifies that a DEQUEUE operation does not wait if no message is available nnspecifies the wait time in seconds Specifies transaction behavior of the DEQUEUE requests On Commit specifies that the DEQUEUE is part of the current transaction The operation is completed when the transaction commits On Commit is the default Immediate specifies that the DEQUEUE is not part of the current transaction The operation constitutes a transaction of its own Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 11 Updating a Message Queue Profile Message Queue Definitions Dequeue Mode Specifies how messages are read from the queue Remove reads the message and removes it from the queue Remove is the default Browse reads a message from the queue but does not remove it After entering the message queue information click Apply The following example shows a newly created message queue profile named UPS and its properties Refer to Figure 6 10 Figure 6 10 Message Queue Profile Example E Oracle Procedural Gateway Visual Workbench for WebSphere MQ x File Edit View Too
122. tory bug stadf34 Updating a Data Profile The following example describes how to update a COBOL data profile 1 Open the PG4MQ Visual Workbench repository Open the Data Profiles folder Open the COBOL folder Select the data profile to update by clicking it once Click the Update icon in the tool bar D m p gt yN The Define COBOL Data Definition dialog box appears See Also Creating a Data Profile on page 6 5 for details about this dialog box 7 Modify the properties as necessary then click Apply 8 A dialog box asks you to confirm the update Refer to Figure 6 7 6 8 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Creating a Message Queue Profile Figure 6 7 Dialog Box to Update a Data Profile E x BE Are you sure you wantto update EMPLOYEE at the repository Cancel 9 Click OK to confirm the update Updating a data profile that is associated with an interface updates the interface profile and the generated MIP Before updating the data profile the PG4MQ Visual Workbench prompts you to confirm the change Removing a Data Profile This example tells how to remove a COBOL data profile 1 Open the PG4MQ Visual Workbench repository Open the Data Profiles folder Open the COBOL folder Select the data profile to update by clicking it once Click the Remove icon in the tool bar eo m RoN A dialog box asks you to confirm the
123. ual Workbench generates data mapping code according to these definitions and the MIP calls this code to convert messages See Also Data Conversion Package on page 7 19 for more information about data profiles and data mapping The message queue profile describes message properties and queuing operations The MIP translates the properties and operations into gateway calls that control the message queuing system when sending or retrieving messages The MIP contains four interface procedures that can be called from any Oracle application QOPEN ENQUEUE DEQUEUE and QCLOSE Each data profile attached to the MIP has its own set of ENQUEUE and DEQUEUE procedures in the MIP The ENQUEUE procedure takes a message on input formatted according to the PL SQL type created for the data profile The DEQUEUE procedure delivers a message as output formatted according to the PL SQL type created for the data profile Note MIP Procedures on page 7 6 for more information QOPEN and QCLOSE QOPEN opens a specified queue You must call QOPEN before calling the advanced ENQUEUE and DEQUEUE procedures See Also Type Definitions on page 7 14 for more information QCLOSE closes an open queue You must call QCLOSE after processing messages with the advanced ENQUEUE and DEQUEUE procedures See Also OCLOSE Procedure on page 7 7 for more information 7 2 Oracle Procedural Gateway Visual Workbench for
124. ue Profile Updating a Message Queue Profile hemoving a Message Queue Profile Creating an Interface Profile and Generating a MIP Testing the Gateway a Compiling the MIP Using the MIP Templates Preparing the MIP for Production Overview of PG4MQ Visual Workbench Development During a typical PG4MQ Visual Workbench development session you do the following 1 Start the Oracle Procedural Gateway Visual Workbench for WebSphere MQ by double clicking its icon in the Start menu list of programs in the Oracle for Microsoft Windows 32 bit folder then connect to the PG4MQ Visual Workbench for WebSphere MQ Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 1 Overview of PG4MQ Visual Workbench Development See Also Starting the PG4MQ Visual Workbench and Connecting to a Repository on page 6 3 for more information 2 Create one or more data profiles for the messages you want to exchange with a non Oracle application For example for COBOL messages import the corresponding COBOL copybook to create the data profile The PG4MQ Visual Workbench maintains data profiles in a repository at the Oracle server See Also Creating a Data Profile on page 6 5 for more information 3 Create one message queue profile to specify how and where inter application messages are to be sent or retrieved The PG4MQ Visual Workbench stores the message queue profile in a repository
125. ural Gateway Visual Workbench for WebSphere MQ 6 19 Testing the Gateway a Click Finish after verifying your selections signaling the PG4MQ Visual Workbench to generate updated MIP code Alternative method for starting the Wizard to update an interface Another way to start the Wizard to update an interface is to 1 Open a PG4MQ Visual Workbench repository Open the Interfaces folder Open the PL SQL folder Select the interface to update m BON Click the Update icon on the tool bar The Update Interface window of the Wizard appears Refer to Figure 6 20 Figure 6 20 Updating an Interface Window for Wizard S Interface Wizard x Updating an interface Select the repository that stores the interface to update JORCL TDEV SUNT X Select the programming language ofthe interface to update PL SQL X Select the interface to update Available Interfaces Click Next to continue Cancel Help Click Next to begin the Wizard s step by step instructions to a Specify a database link a Create a new data profile or update the selected data profile as necessary a Create a new message queue profile or update the selected message queue profile Click Finish after verifying your selections signaling the PG4MQ Visual Workbench to generate updated MIP code If you need help using the Wizard then click Help Testing the Gateway When you create a MIP the PG4MQ Visual Workbench generates gateway test co
126. user and all repository tables This step creates the administrative user for the Visual Workbench repository as PGMADMIN with an initial password of PGMADMIN This user owns all objects in the repository After this step a private Visual Workbench repository which includes the PGM SUP PGM BOM and PGM UTL8 packages is created in the Oracle integrating server which only the user PGMADMIN can access Step 6 Create public synonyms and development roles This is an optional step to change the private access privileges of the Visual Workbench repository The private status allows only the PGMADMIN user to have access to the repository If you enter N and press Enter then the repository retains its private status A public status allows the granting of access privileges to other users besides PGMADMIN If you want to give the repository public status then enter Y and press Enter After the Repository Is Created After creating the Visual Workbench repository there is one optional step Grant development privileges for the Visual Workbench repository to users To allow users other than PGMADMIN to perform development operations on the Visual Workbench repository PEMADMIN must grant them the necessary privileges To do this perform the following Ensure that the repository has a public status It has this status if you created it by using Steps 1 through 6 of the pgvwbrepos sql script If you did not use Step 6 then rerun the scrip
127. ve a custom Oracle installation then ask the DBA who installed the Oracle server for the port number System identifier SID for the Oracle server where the repository is installed It is usually the default of ORCL the SID used for standard Oracle installations If you have a custom Oracle installation then ask the DBA who installed the Oracle server for the correct SID After connecting to a PG4MQ Visual Workbench repository the PG4MQ Visual Workbench window shows information about it Refer to Figure 6 2 Using the Oracle Procedural Gateway Visual Workbench for WebSphere MQ 6 3 Creating a Data Profile Figure 6 2 PG4MQ Visual Workbench Repository Window Ei Procedural Gateway for Message Queuing Visual Workbench Joj x File Edit View Tools Help E E LJ a 4 nm M X 9 e M cw Open Close reste Create fete Ren t Add Refresh p e Test wizard L3 REPOSITORIES ORCL ITDEV SUN7 1 8 DATA PROFILES T MESSAGE QUEUE PROFILES x INTERFACES 0804 ITDEV SUN7 T Connection Info User Name pgmadmin TCP Host Name ITDEV SUN7 TCP Port Number 1521 Oracle SID ORCL Repository Name ORCL ITDEV SUN7 Repository Version Last Updated Last Change 40001 10 mar 99 14 45 install The example shows the expanded PG4MQ Visual Workbench repository directory tree and the General panel containing connect information and the repository s history of events Select the Database tab to see information about the Oracle ser
128. ver where the PG4MOQ Visual Workbench repository resides To connect to another repository click the Open icon in the tool bar and enter the suitable connect information in the dialog box You can start actions in the PG4MQ Visual Workbench by clicking the suitable icons in the tool bar or by opening the suitable drop down list on the menu bar then selecting a function from the menu For example to connect to a repository you can open the File menu and select Open Repository Creating a Data Profile After connecting to a PG4MQ Visual Workbench repository create one or more data profiles Data profiles define the data definitions that the PG4MQ Visual Workbench uses to map between non Oracle data types and PL SQL data types The PG4MQ Visual Workbench maintains these definitions in the repository at the Oracle server For example to create a COBOL data profile in a PG4MQ Visual Workbench repository 1 Open the repository by double clicking the repository icon or by clicking the plus sign next to the repository icon 6 4 Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User s Guide Creating a Data Profile Figure 6 3 Click the plus icon to open the repository ms Oracle Procedural Gateway Visual Workbench for WebSphere MQ File Edit View Tools Help 3 REPOSITORIES 34 PROFILES MESSAGE QUEUE PROFILES INTERFACES stall 2 Open the Data Profiles folder 3 Select the CO
129. ype definition msgid OUT RAW The following table describes the parameters that need to be specified Parameter Description queue name Name of the queue from which to retrieve the message dequeue options Defines the DEQUEUE options that can be specified by the calling application See Also DEQUEUE OPTIONS Typ on page 7 15 message properties Returns the message options for a message See Also MESSAGE _ PROPERTIES Typ on page 7 16 payload Returns the message data formatted according to type definition type definition See Also Data Conversion Package on page 7 19 for information msgid Identifier generated by the message queuing system for the message Example In this example the queue from which messages are to be retrieved is opened using QOPEN In the example openOpts open mode is set to REMOVE so that each time an ENQUEUE procedure is called messages are removed permanently from the queue You can set openOpts open mode to BROWSE to enable reading a message from the queue and not removing it A handle to the queue is returned and is used as an input parameter in each DEQUEUE call In this example the message contains an employee record defined by a data profile named EMPLOYEE It is passed as an output argument to the advanced DEQUEUE procedure of a MIP named HIRE The queue name and the DEQUEUE mode are explicitly specified by the calling application and passed in using the queue and dequeueOpts input param

Oracle Procedural Gateway Visual Workbench For WebSphere MQ

Contents

Download Pdf Manuals

Related Search

Related Contents