Writing OLAP DML Data to a Relational Table

PREPARE and EXECUTE statements

You write data stored in OLAP DML variables into a relational table by using those variables as input host variables in your SQL statements. When writing multiple records, you should use the PREPARE and EXECUTE statements so that the same INSERT statement does not have to be recompiled for each row of data being sent to the table.

The syntax of the PREPARE and EXECUTE statements is shown below.

SQL PREPARE statement-name FROM sql-statement
SQL EXECUTE statement-name

PREPARE and EXECUTE must appear in the same program and cannot include ampersand (&) substitution. When an input host variable is a dimension, you must use a FOR command to loop over its values; otherwise, only the first value in status is sent.

Example: Inserting OLAP DML data into a relational table

Suppose that you have been using the OLAP DML to plan the introduction of a new product line, and now you want to add these new products to your relational database. You could copy this information from your analytic workspace into the appropriate relational table.

OLAP DML objects

The following are the OLAP DML objects used to store the data.

DEFINE PROD DIMENSION TEXT
DEFINE LABELS.P VARIABLE TEXT <PROD>
DEFINE SUGGEST.P VARIABLE SHORT <PROD>

Inefficient FOR loop

The following program fragment shows how you would use a FOR loop so that all product values currently in status are copied to a table named Products.

for prod
   do
      sql insert into products -
         values(:prod, :labels.p, :suggest.p)
      if sqlcode ne 0
         then break
   doend

Efficient precompiled code

The previous example will run much more efficiently when the INSERT statement is compiled with the PREPARE statement. The next example shows the PREPARE statement being used to compile the INSERT statement with a name of WRITE_PRODUCTS, which is then run by an EXECUTE statement within the FOR loop.

sql prepare write_products from -
   insert into products -
      values(:prod, :labels.p, :suggest.p)
      .
      .
      .
for prod
   do
      sql execute write_products
      if sqlcode ne 0
         then break
   doend

Example: Conditionally updating a relational table

You can also use the values of a multidimensional OLAP DML variable to update the values in a relational table. Using a FOR loop, your OLAP DML program steps through the specified dimension value by value and uses a WHERE clause to point to the corresponding row in the relational table.

The following program fragment updates only those rows in the Products table where the values in the PROD_ID column match the PROD dimension values currently in status.

for prod
   do
      sql update products -
      set Suggested_Price = :suggest.p -
         where Prod_ID = :prod
      if sqlcode ne 0
         then break
   doend