Using VSAM Operations

The database operations used in the VSAM Adapter are used to access the VSAM database. Database operations are either accessed through activities in BPEL, or through methods called from a JCD Collaboration.

• VSAM Adapter Database Operations (BPEL)

• VSAM Adapter Database Operations (JCD)

VSAM Adapter Database Operations (BPEL)

The VSAM Adapter uses a number operations to query the VSAM database. Within a BPEL business process, the VSAM Adapter uses BPEL activities to perform basic outbound database operations, including:

• Insert

• Update

• Delete

• SelectOne

• SelectMultiple

• SelectAll

In addition to these outbound operations, the VSAM Adapter also employs the inbound activity ReceiveOne within a Prepared Statement OTD.

Activity Input and Output

The Java CAPS Business Rules Designer includes Input and Output columns to map and transform data between activities displayed on the Business Process Canvas.

Figure 1-8 displays the business rules between the FileClient.write and otdVSAM.CICSEMPDelete activities. In this example, the whereClause attribute appears on the Input side.

Figure 1-8 Input and Output Between Activities

The following table lists the expected Input and Output of each database operation activity.

Table 1-7 VSAM Operations

VSAM Adapter Database Operations (JCD)

The same database operations are also used in the JCD, but appear as methods to call from the Collaboration.

Tables and Views are manipulated through OTDs. Methods to call include:

• insert()

• insertRow()

• update(String sWhere)

• updateRow()

• delete(String sWhere)

• deleteRow()

• select(String where)

  ---

  Note - Refer to the Javadoc for a full description of methods included in the VSAM Adapter.

  ---

The Table

A table OTD represents a database table. It consists of fields and methods. Fields correspond to the columns of a table while methods are the operations that you can apply to the OTD. This allows you to perform query, update, insert, and delete SQL operations in a table. The ability to update via a resultset is called “Updatable Resultset”, which is a feature supported by this adapter.

By default, the Table OTD has UpdatableConcurrency and ScrollTypeForwardOnly. Normally you do not have to change the default setting.

The type of result returned by the select() method can be specified using:

• SetConcurrencytoUpdatable

• SetConcurrencytoReadOnly

• SetScrollTypetoForwardOnly

• SetScrollTypetoScrollSensitive

• SetScrollTypetoInsensitive

To Perform a Query Operation on a Table

1. Execute the select() method with the where clause specified if necessary.
2. Loop through the ResultSet using the next() method.
3. Process the return record within a while() loop.

   For example:

   package prjVSAM_JCDjcdALL;
   
   public class jcdTableSelect
   {
   
       public com.stc.codegen.logger.Logger logger;
   
       public com.stc.codegen.alerter.Alerter alerter;
   
       public com.stc.codegen.util.CollaborationContext collabContext;
   
       public com.stc.codegen.util.TypeConverter typeConverter;
   
       public void receive( com.stc.connector.appconn.file.
   FileTextMessage input, otdVSAM.OtdVSAMOTD otdVSAM_1, dtd.
   otdOutputDTD_1935483687.Emp otdOutputDTD_Emp_1, com.stc.connector.
   appconn.file.FileApplication FileClient_1 )
           throws Throwable
       {
           FileClient_1.setText( "Selecting record(s) from CICSEMP
   table via table select .." );
           FileClient_1.write();
           otdVSAM_1.getCICSEMP().select( input.getText() );
           while (otdVSAM_1.getCICSEMP().next()) {
               otdOutputDTD_Emp_1.setENAME( otdVSAM_1.getCICSEMP().getENAME() );
               otdOutputDTD_Emp_1.setPHONE( typeConverter.intToString( otdVSAM_1.
   getCICSEMP().getPHONE(), "#", false, "" ) );
               otdOutputDTD_Emp_1.setMAILID( otdVSAM_1.getCICSEMP().getMAILID() );
               otdOutputDTD_Emp_1.setSALARY( otdVSAM_1.getCICSEMP().getSALARY().
   toString() );
               otdOutputDTD_Emp_1.setJOBID( typeConverter.doubleToString(
    otdVSAM_1.getCICSEMP().getJOBID(), "#.000000;-#.000000", false, "" ) );
               otdOutputDTD_Emp_1.setEMPID( typeConverter.intToString(
    otdVSAM_1.getCICSEMP().getEMPID(), "#", false, "" ) );
               otdOutputDTD_Emp_1.setDEPTID( typeConverter.shortToString(
    otdVSAM_1.getCICSEMP().getDEPTID(), "#", false, "" ) );
               otdOutputDTD_Emp_1.setDEPARTMENT( otdVSAM_1.getCICSEMP().getDEPARTMENT() );
               FileClient_1.setText( otdOutputDTD_Emp_1.marshalToString() );
               FileClient_1.write();
           }
           FileClient_1.setText( "Done table select." );
           FileClient_1.write();
       }
   
   }

To Perform an Insert Operation on a Table

1. Execute the insert() method. Assign a field.
2. Insert the row by calling insertRow().

   This example inserts an employee record:

   package prjVSAM_JCDjcdALL;
   
   public class jcdInsert
   {
   
       public com.stc.codegen.logger.Logger logger;
   
       public com.stc.codegen.alerter.Alerter alerter;
   
       public com.stc.codegen.util.CollaborationContext collabContext;
   
       public com.stc.codegen.util.TypeConverter typeConverter;
   
       public void receive( com.stc.connector.appconn.file.FileTextMessage input,
    dtd.otdInputDTD_622919076.Emp otdInputDTD_Emp_1, otdVSAM.OtdVSAMOTD otdVSAM_1,
    com.stc.connector.appconn.file.FileApplication FileClient_1 )
           throws Throwable
       {
           FileClient_1.setText( "Inserting records into CICSEMP table .." );
           FileClient_1.write();
           otdInputDTD_Emp_1.unmarshalFromString( input.getText() );
           otdVSAM_1.getCICSEMP().insert();
           for (int i1 = 0; i1 < otdInputDTD_Emp_1.countX_sequence_A(); i1 += 1) {
               otdVSAM_1.getCICSEMP().setENAME(
    otdInputDTD_Emp_1.getX_sequence_A( i1 ).getENAME() );
               otdVSAM_1.getCICSEMP().setPHONE( typeConverter.stringToInt(
    otdInputDTD_Emp_1.getX_sequence_A( i1 ).getPHONE(), "#", false, 0 ) );
               otdVSAM_1.getCICSEMP().setMAILID( otdInputDTD_Emp_1.getX_sequence_A(
    i1 ).getMAILID() );
               otdVSAM_1.getCICSEMP().setSALARY( new java.math.BigDecimal(
    otdInputDTD_Emp_1.getX_sequence_A( i1 ).getSALARY() ) );
               otdVSAM_1.getCICSEMP().setJOBID( typeConverter.stringToDouble(
    otdInputDTD_Emp_1.getX_sequence_A( i1 ).getJOBID(), "#.000000;-#.000000",
    false, 0 ) ); otdVSAM_1.getCICSEMP().setEMPID( typeConverter.stringToInt(
    otdInputDTD_Emp_1.getX_sequence_A( i1 ).getEMPID(), "#", false, 0 ) );
               otdVSAM_1.getCICSEMP().setDEPTID( typeConverter.stringToShort(
    otdInputDTD_Emp_1.getX_sequence_A( i1 ).getDEPTID(), "#", false, 0 ) );
               otdVSAM_1.getCICSEMP().setDEPARTMENT( otdInputDTD_Emp_1.getX_sequence_A(
    i1 ).getDEPARTMENT() );
               otdVSAM_1.getCICSEMP().insertRow();
           }
           FileClient_1.setText( "Done Insert." );
           FileClient_1.write();
       }
   }

To Perform an Update Operation on a Table

1. Execute the update() method.
2. Using a while loop together with next(), move to the row that you want to update.
3. Assign updating value(s) to the fields of the table OTD.
4. Update the row by calling updateRow().

   package prjVSAM_JCDjcdALL;
   
   public class jcdUpdate
   {
   
       public com.stc.codegen.logger.Logger logger;
   
       public com.stc.codegen.alerter.Alerter alerter;
   
       public com.stc.codegen.util.CollaborationContext collabContext;
   
       public com.stc.codegen.util.TypeConverter typeConverter;
   
       public void receive( com.stc.connector.appconn.file.FileTextMessage
   input, otdVSAM.OtdVSAMOTD otdVSAM_1, dtd.otdOutputDTD_1935483687.
   Emp otdOutputDTD_Emp_1, com.stc.connector.appconn.file.FileApplication FileClient_1 )
           throws Throwable
       {
           FileClient_1.setText( "Update the Department .. " );
           FileClient_1.write();
           otdVSAM_1.getCICSEMP().update( input.getText() );
           while (otdVSAM_1.getCICSEMP().next()) {
               otdVSAM_1.getCICSEMP().setDEPARTMENT( "QAQAQA" );
               otdVSAM_1.getCICSEMP().updateRow();
           }
           FileClient_1.setText( "Done Update." );
           FileClient_1.write();
       }
   
   }

To Perform a Delete Operation on a Table

• Execute the delete() method.

  In this example DELETE an employee.

  package prjVSAM_JCDjcdALL;
  
  public class jcdDelete
  {
  
      public com.stc.codegen.logger.Logger logger;
  
      public com.stc.codegen.alerter.Alerter alerter;
  
      public com.stc.codegen.util.CollaborationContext collabContext;
  
      public com.stc.codegen.util.TypeConverter typeConverter;
  
      public void receive( com.stc.connector.appconn.file.
  FileTextMessage input, otdVSAM.OtdVSAMOTD otdVSAM_1, dtd.
  otdOutputDTD_1935483687.Emp otdOutputDTD_Emp_1, com.stc.connector.
  appconn.file.FileApplication FileClient_1 )
          throws Throwable
      {
          FileClient_1.setText( "Delete record .." );
          FileClient_1.write();
          otdVSAM_1.getCICSEMP().delete( input.getText() );
          FileClient_1.setText( "Done delete." );
          FileClient_1.write();
      }
  
  }

Prepared Statement

A Prepared Statement OTD represents a SQL statement that has been compiled. Fields in the OTD correspond to the input values that users need to provide.

Prepared statements can be used to perform insert, update, delete and query operations. A prepared statement uses a question mark (?) as a place holder for input. For example:

insert into EMP_TAB(Age, Name, Dept No) value(?, ?, ?)

To execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.

getPrepStatement().getPreparedStatementTest().setAge(23);
getPrepStatement().getPreparedStatementTest().setName(”Peter Pan’);
getPrepStatement().getPreparedStatementTest().setDeptNo(6);
getPrepStatement().getPreparedStatementTest().executeUpdate();

Batch Operations

To achieve better performance, consider using a bulk insert if you have to insert many records. This is the “Add Batch” capability. The only modification required is to include the addBatch() method for each SQL operation and then the executeBatch() call to submit the batch to the database server. Batch operations apply only to Prepared Statements.

getPrepStatement().getPreparedStatementTest().setAge(23);
getPrepStatement().getPreparedStatementTest().setName(”Peter Pan’);
getPrepStatement().getPreparedStatementTest().setDeptNo(6);
getPrepStatement().getPreparedStatementTest().addBatch();

getPrepStatement().getPreparedStatementTest().setAge(45);
getPrepStatement().getPreparedStatementTest().setName(”Harrison Ford’);
getPrepStatement().getPreparedStatementTest().setDeptNo(7);
getPrepStatement().getPreparedStatementTest().addBatch();
getPrepStatement().getPreparedStatementTest().executeBatch();