Workflow for running Database Designer programmatically

The following example shows the steps you take to create a design by running Database Designer programmatically.

Important

Before running Database Designer functions on an existing schema, back up the current design by calling function EXPORT_CATALOG.

Before you run this example, you should have the DBDUSER role, and you should have enabled that role using the SET ROLE DBDUSER command:

Create a table in the public schema:

=> CREATE TABLE T(
   x INT,
   y INT,
   z INT,
   u INT,
   v INT,
   w INT PRIMARY KEY
   );

Add data to the table:

\! perl -e 'for ($i=0; $i<100000; ++$i)   {printf("%d, %d, %d, %d, %d, %d\n", $i/10000, $i/100, $i/10, $i/2, $i, $i);}'
   | vsql -c "COPY T FROM STDIN DELIMITER ',' DIRECT;"

Create a second table in the public schema:

=> CREATE TABLE T2(
   x INT,
   y INT,
   z INT,
   u INT,
   v INT,
   w INT PRIMARY KEY
   );

Copy the data from table T1 to table T2 and commit the changes:

=> INSERT /*+DIRECT*/ INTO T2 SELECT * FROM T;
=> COMMIT;

Create a new design:
```
=> SELECT DESIGNER_CREATE_DESIGN('my_design');
```
This command adds information to the DESIGNS system table in the V_MONITOR schema.

Add tables from the public schema to the design :

=> SELECT DESIGNER_ADD_DESIGN_TABLES('my_design', 'public.t');
=> SELECT DESIGNER_ADD_DESIGN_TABLES('my_design', 'public.t2');

These commands add information to the DESIGN_TABLES system table.

Create a file named queries.txt in /tmp/examples, or another directory where you have READ and WRITE privileges. Add the following two queries in that file and save it. Database Designer uses these queries to create the design:
```
SELECT DISTINCT T2.u FROM T JOIN T2 ON T.z=T2.z-1 WHERE T2.u > 0;
SELECT DISTINCT w FROM T;
```

Add the queries file to the design and display the results—the numbers of accepted queries, non-design queries, and unoptimizable queries:

=> SELECT DESIGNER_ADD_DESIGN_QUERIES
     ('my_design',
     '/tmp/examples/queries.txt',
     'true'
     );

The results show that both queries were accepted:

Number of accepted queries                      =2
Number of queries referencing non-design tables =0
Number of unsupported queries                   =0
Number of illegal queries                       =0

The DESIGNER_ADD_DESIGN_QUERIES function populates the DESIGN_QUERIES system table.

Set the design type to comprehensive. (This is the default.) A comprehensive design creates an initial or replacement design for all the design tables:
```
=> SELECT DESIGNER_SET_DESIGN_TYPE('my_design', 'comprehensive');
```
Set the optimization objective to query. This setting creates a design that focuses on faster query performance, which might recommend additional projections. These projections could result in a larger database storage footprint:
```
=> SELECT DESIGNER_SET_OPTIMIZATION_OBJECTIVE('my_design', 'query');
```
Create the design and save the design and deployment scripts in /tmp/examples, or another directory where you have READ and WRITE privileges. The following command:
- Analyzes statistics
- Doesn't deploy the design.
- Doesn't drop the design after deployment.
- Stops if it encounters an error.
```
=> SELECT DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY
   ('my_design',
    '/tmp/examples/my_design_projections.sql',
    '/tmp/examples/my_design_deploy.sql',
    'True',
    'False',
    'False',
    'False'
    );
```
This command adds information to the following system tables:

Examine the status of the Database Designer run to see what projections Database Designer recommends. In the deployment_projection_name column:

rep indicates a replicated projection

super indicates a superprojection

The deployment_status column is pending because the design has not yet been deployed.

For this example, Database Designer recommends four projections:

=> \x
Expanded display is on.
=> SELECT * FROM OUTPUT_DEPLOYMENT_STATUS;
-[ RECORD 1 ]--------------+-----------------------------
deployment_id              | 45035996273795970
deployment_projection_id   | 1
deployment_projection_name | T_DBD_1_rep_my_design
deployment_status          | pending
error_message              | N/A
-[ RECORD 2 ]--------------+-----------------------------
deployment_id              | 45035996273795970
deployment_projection_id   | 2
deployment_projection_name | T2_DBD_2_rep_my_design
deployment_status          | pending
error_message              | N/A
-[ RECORD 3 ]--------------+-----------------------------
deployment_id              | 45035996273795970
deployment_projection_id   | 3
deployment_projection_name | T_super
deployment_status          | pending
error_message              | N/A
-[ RECORD 4 ]--------------+-----------------------------
deployment_id              | 45035996273795970
deployment_projection_id   | 4
deployment_projection_name | T2_super
deployment_status          | pending
error_message              | N/A

View the script /tmp/examples/my_design_deploy.sql to see how these projections are created when you run the deployment script. In this example, the script also assigns the encoding schemes RLE and COMMONDELTA_COMP to columns where appropriate.
Deploy the design from the directory where you saved it:
```
=> \i /tmp/examples/my_design_deploy.sql
```
Now that the design is deployed, delete the design:
```
=> SELECT DESIGNER_DROP_DESIGN('my_design');
```