Hello Gopi,
Why do you want to use userexits for Tablespace?
I'm asking, because I have a customer, that use Oracle, and for some unknown reasons for me (except that Oracle DBA are strange people...) they insisted to have the oracle DB outside from CMOD server.
This is something we normally don't do, CMOD and DB should be in the same server for performance and also for maintenance point of view.
Why am I explaning that? CMOD will automatically create for you tablespace, tables, indexes. Remove them if needed.
Everything would be automatically done for you.
By starting to use the user exit for tablespace, you will need to do everything the CMOD does for you automatically, manually...
Instead of helping you, it would be your role to ensure that what you do will work with CMOD.
Of course, if you play too much then CMOD works in a strange way... then you might be out of luck with IBM support, depending on what you have done...
My advice, open a PMR with IBM Support and ask them if what you want to do is ok with the CMOD philosophy, otherwise you might have a problem when the CMOD developpers wants to change something, and then what you did, just break in the new versions...
Starting with CMOD 8.5.0.3, you have an extension of this user exits, that allows you also to catch all the "alter/drop tablespace/tables/indexes" that CMOD might want to do from time to time... Before 8.5.0.3 it was not possible... only the create one were ok.
The documentation in the .h header is quite clear:
/**********************************************************************/
/* TBLSPCRT - Tablespace Create Exit */
/* */
/* To activate the tablespace creation exit, set the following */
/* variable in the appropriate OnDemand instance ars.cfg file: */
/* */
/* ARS_DB_TABLESPACE_USEREXIT=<absolute_dll_path_name> */
/* */
/* INPUT: appl_grp */
/* tblsp_name */
/* table_name */
/* idx_name */
/* sql (allocated with 16384 bytes) */
/* action */
/* instance */
/* */
/* OUTPUT: */
/* */
/* 1) OnDemand will invoke the exit with action == 1 */
/* so that the exit can create the tablespace (tblsp_name) */
/* using (sql) */
/* *created -> 0 exit did not create the tablespace, */
/* OnDemand needs to create the tablespace */
/* using (sql), which can be left unchanged */
/* or modified by the exit */
/* *created -> 1 exit created the tablespace */
/* */
/* 2) OnDemand will then invoke the exit with action == 2 */
/* so that the exit can create the table (table_name) */
/* inside of the tablespace (tblsp_name) using (sql) */
/* *created -> 0 exit did not create the table, */
/* OnDemand needs to create the table */
/* using (sql), which can be left unchanged */
/* or modified by the exit */
/* *created -> 1 exit created the table */
/* */
/* 3) OnDemand will then invoke the exit with action == 3 */
/* so that the exit can create the table indexes (idx_name) */
/* inside of the tablespace (tblsp_name) for table */
/* (table_name) using (sql). This will be invoked based */
/* inside of the tablespace (tblsp_name) for table */
/* (table_name) using (sql). This will be invoked based */
/* on the number of indexes to create for the appl_grp */
/* *created -> 0 exit did not create the index, */
/* OnDemand needs to create the index */
/* using (sql), which can be left unchanged */
/* or modified by the exit */
/* *created -> 1 exit created the index */
/* */
/* 4) OnDemand will then invoke the exit with action == 4 */
/* so that the exit can perform any additional work */
/* *created -> Is not used */
/* sql -> If sql is not an empty string, OnDemand */
/* will issue (sql) to the database */
/* */
/* If ARS_DB_TABLESPACE_USEREXIT_EXTRA=1 is defined in */
/* ars.cfg, then the following actions will also be invoked */
/* when OnDemand needs to do further actions: */
/* */
/* 5) OnDemand will invoke the exit with action == 5 */
/* so that the exit can drop the tablespace (tblsp_name) */
/* using (sql) */
/* *created -> 0 exit did not drop the tablespace, */
/* OnDemand needs to drop the tablespace */
/* using (sql), which can be left unchanged */
/* or modified by the exit */
/* *created -> 1 exit dropped the tablespace */
/* */
/* 6) OnDemand will invoke the exit with action == 6 */
/* so that the exit can drop the table (table_name) */
/* using (sql) when OnDemand needs to drop a table */
/* *created -> 0 exit did not drop the table, */
/* OnDemand needs to drop the table */
/* using (sql), which can be left unchanged */
/* or modified by the exit */
/* *created -> 1 exit dropped the table */
/* */
/* 7) OnDemand will invoke the exit with action == 7 */
/* so that the exit can drop the index (idx_name) */
/* using (sql) */
/* *created -> 0 exit did not drop the index, */
/* OnDemand needs to drop the index */
/* using (sql), which can be left unchanged */
/* OnDemand needs to drop the index */
/* using (sql), which can be left unchanged */
/* or modified by the exit */
/* *created -> 1 exit dropped the index */
/* */
/* 8) OnDemand will invoke the exit with action == 8 */
/* so that the exit can alter the table (table_name) */
/* using (sql) */
/* *created -> 0 exit did not alter the table, */
/* OnDemand needs to alter the table */
/* using (sql), which can be left unchanged */
/* or modified by the exit */
/* *created -> 1 exit altered the table */
/* */
/* RETURN_CODE: */
/* 0 -> Successful */
/* Otherwise -> Failed */
/* */
/**********************************************************************/
ArcI32
ARSCSXIT_EXPORT
ARSCSXIT_API
TBLSPCRT( ArcCSXitApplGroup *appl_grp,
char *tblsp_name,
char *table_name,
char *idx_name,
char *sql,
ArcI32 action,
ArcI32 *created,
char *instance
);
You won't find anything else, than what you have in this exits directory... unfortunately.
With my customer we did just that, experiment. Until they were confident that it worked as they expected.
I would just suggest that you test it in a developpement server, and test it very very very carefully, because each customer has it's own requirements.
Unfortunately I cannot reproduce the code they use.
But I can show you a template of what they do basically:
#define _ARSUSTBL_C
/*********************************************************************/
/* */
/* MODULE NAME: ARSUSTBL.C */
/* */
/* */
/* SYNOPSIS: OnDemand TableSpace Creation Exit */
/* */
/* */
/* DESCRIPTION: This module contains the Table Space creation */
/* function. */
/* */
/* COPYRIGHT: */
/* 5724-J33 (C) COPYRIGHT IBM CORPORATION 2007. */
/* All Rights Reserved */
/* Licensed Materials - Property of IBM */
/* */
/* US Government Users Restricted Rights - Use, duplication or */
/* disclosure restricted by GSA ADP Schedule Contract with IBM Corp.*/
/* */
/* NOTE: This program sample is provided on an as-is basis. */
/* The licensee of the OnDemand product is free to copy, */
/* revise modify, and make derivative works of this program */
/* sample as they see fit. */
/* */
/*********************************************************************/
#include <arscsxit.h>
ArcI32
ARSCSXIT_EXPORT
ARSCSXIT_API
TBLSPCRT( ArcCSXitApplGroup *appl_grp,
ArcChar *tblsp_name,
ArcChar *table_name,
ArcChar *idx_name,
ArcChar *sql,
ArcI32 action,
ArcI32 *created,
ArcChar *instance
)
{
ArcI32 rc;
if (action == 1) { /* Creation TBLSpace */
rc=do_something_tbsp;
*created=1; /* CMOD will aknowledge the change but do nothing */
}
if (action == 2) { /* Creation Table */
rc=do_something_table;
*created=1; /* CMOD will aknowledge the change but do nothing */
}
...
if (action == 6) { /* Drop Table */
sql=change_sql_for_fun(sql);
*created=0; /* Let CMOD do it himself with our corrected sql */
}
if (action == 7) { /* Drop Indexes */
rc=drop_something_indexes;
*created=1; /* CMOD will aknowledge the change but do nothing */
}
if (action == 8) { /* Alter Table */
rc=alter_something_table;
*created=1; /* CMOD will aknowledge the change but do nothing */
}
return( rc );
}
Basically there is no more than that...
Now your turn, to test, test, test, test, test, test, test, test, test, test... and maybe again test.
Sincerely yours,
Alessandro
