cmod tablespaces.key

ODWS2015
tablespaces
cmod workshop 2015, Germany, Böblingen
Using tablespaces
larger than 4k
ARSUTBL exit
cmod workshop 2015, Germany, Böblingen
why should i need a larger tablespace?
•
•
•
•
•
A new cmod mp installation creates a DB2 tablespace of 4K by
default
CMOD gives the possibility to uses fields larger than 256
characters
Email archiving usually needs fields longer than 256 characters
(receiver, subject..)
Creating an application Group is possible but creating the table
will fail
Extending an existing application group is possible without an
error message
cmod workshop 2015, Germany, Böblingen
why should i need a larger tablespace?
•
•
•
Creating an application Group is possible but the first load will fail
Existing application group may be extended with more fields
Strange results if more fields are added to an application group later
DB Error: [IBM][CLI Driver][DB2/NT64] SQL0204N "ODADMIN.CBA1" ist ein nicht definierter Name. SQLSTATE=42704 -- SQLSTATE=42S02, SQLCODE=-204,
File=arsseg.c, Line=1472
DB Error: [IBM][CLI Driver][DB2/NT64] SQL0670N Die Anweisung ist fehlgeschlagen, da die Zeilenlänge der Ergebnistabelle die Zeilenbegrenzung
überschritten hätte. Grenzwert: "4005". Tabellenbereich: "ODADMIN_CBA1". SQLSTATE=54010 -- SQLSTATE=54010, SQLCODE=-670, File=arsseg.c,
Line=3670
Activities: Current(0)
Activities: Current(3)
DB Error: [IBM][CLI Driver][DB2/NT64] SQL0670N Die Anweisung ist fehlgeschlagen, da die Zeilenlänge der Ergebnistabelle die Zeilenbegrenzung überschritten hätte.
Grenzwert: "4005". Tabellenbereich: "ODADMIN_CBA1". SQLSTATE=54010 -- SQLSTATE=54010, SQLCODE=-670, File=arsseg.c, Line=3670
DB Error: [IBM][CLI Driver][DB2/NT64] SQL0670N Die Anweisung ist fehlgeschlagen, da die Zeilenlänge der Ergebnistabelle die Zeilenbegrenzung überschritten hätte.
Grenzwert: "4005". Tabellenbereich: "ODADMIN_CBA1". SQLSTATE=54010 -- SQLSTATE=54010, SQLCODE=-670, File=arsseg.c, Line=3670
DB Error: [IBM][CLI Driver][DB2/NT64] SQL0670N Die Anweisung ist fehlgeschlagen, da die Zeilenlänge der Ergebnistabelle die Zeilenbegrenzung überschritten hätte.
Grenzwert: "4005". Tabellenbereich: "ODADMIN_CBA1". SQLSTATE=54010 -- SQLSTATE=54010, SQLCODE=-670, File=arsseg.c, Line=3670
DB Error: [IBM][CLI Driver][DB2/NT64] SQL0670N Die Anweisung ist fehlgeschlagen, da die Zeilenlänge der Ergebnistabelle die Zeilenbegrenzung überschritten hätte.
Grenzwert: "4005". Tabellenbereich: "ODADMIN_CBA1". SQLSTATE=54010 -- SQLSTATE=54010, SQLCODE=-670, File=arsseg.c, Line=3670
DB Error: [IBM][CLI Driver][DB2/NT64] SQL0670N Die Anweisung ist fehlgeschlagen, da die Zeilenlänge der Ergebnistabelle die Zeilenbegrenzung überschritten hätte.
Grenzwert: "4005". Tabellenbereich: "ODADMIN_CBA1". SQLSTATE=54010 -- SQLSTATE=54010, SQLCODE=-670, File=arsseg.c, Line=3670
cmod workshop 2015, Germany, Böblingen
The solution: ARSUTBL exit
install Microsoft visual Studio express
or any other c++ compiler on your
cmod server
cmod workshop 2015, Germany, Böblingen
Modify the arsutbl.c exit
IBM delivers the sample files in the cmod installation directory:
D:\Program Files\IBM\OnDemand for Windows\V9.0\exits
Open the file with the c++ editor
cmod workshop 2015, Germany, Böblingen
Modify the arsutbl.c exit
IBM delivers the sample files in the cmod installation directory:
D:\Program Files\IBM\OnDemand for Windows\V9.0\exits
Open the file with the c++ editor
the default ist just an empty exit:
#pragma export(TBLSPCRT)
#include "arscsxit.h"
int TBLSPCRT( ArcCSXitApplGroup appl_grp,
char tblsp_name,
char table_name,
char idx_name,
char sql,
int
action,
int
created
)
cmod workshop 2015, Germany, Böblingen
Modify the arsutbl.c exit
Extend the #include section and add the following #include statements
#include
#include
#include
#include
<arscsxit.h>
<stdio.h>
<string.h>
<stdlib.h>
cmod workshop 2015, Germany, Böblingen
add the create tablespace function
{
ArcI32 rc;
rc = 0;
*created = 0;
if (action == 1) {
char *space = " ";
char *location;
// Prepare the final SQL variable
char *finalsql = malloc(2048);
finalsql[0] = '\0';
// Jump over 'CREATE'
location = strpbrk(sql, space);
// Jump over 'TABLESPACE'
location = strpbrk(location + 1, space);
// Jump over name of Table Space
location = strpbrk(location + 1, space);
// Copy the first part into the final SQL (CREATE TABLESPACE <name>)
strncat(finalsql, sql, (location - sql) + 1);
// Append the "PAGESIZE 8K" string
strncat(finalsql, "PAGESIZE 8K", strlen("PAGESIZE 8K") + 1);
// Append the rest of the string until the end
strncat(finalsql, location, strlen(location));
// Finally at the end, add the location of the 8K bufferpool defined in DB2 (here 'BP8k')
strncat(finalsql, " BUFFERPOOL BP8k", strlen(" BUFFERPOOL BP8k") + 1);
// Some logs of what happens
// First line, the original SQL string that CMOD wants to use to create the tablespace
// Second line, then final SQL string to be given to CMOD for really creating the tablespace
FILE *fp;
fp = fopen("C:\\temp\\log.log", "w");
fwrite(sql, 1, strlen(sql), fp);
fwrite("\r\n", 1, 2, fp);
fwrite(finalsql, 1, strlen(finalsql), fp);
fwrite("\r\n", 1, 2, fp);
fclose(fp);
}
// Overwrite the original SQL variable with our Final SQL content
sql[0] = '\0';
strncat(sql, finalsql, strlen(finalsql) + 1);
// Free some memory in order to avoid memory leaks...
free(finalsql);
cmod workshop 2015, Germany, Böblingen
then compile the arsutbl.c exit
copy the dll into the bin directory of your cmod server.
No other path will work !!
cmod workshop 2015, Germany, Böblingen
add the exit to cmod environment
open the registry and add a new key to the cfg
D:\Program Files\IBM\OnDemand for Windows\V9.0\bin\arsutbl.dll
cmod workshop 2015, Germany, Böblingen
create the buffer pool
open the db2 command window
connect to ARCHIVE enter
create bufferpool BP8K immediate pagesize 8k enter
connect reset enter
restart the cmod service
cmod workshop 2015, Germany, Böblingen
create the application group table
Load a document into cmod
the application group should be created now
To verify which bufferpool was used enter: list tablespaces show detail
cmod workshop 2015, Germany, Böblingen
Questions?
thanks to Allessandro Perucci (IBM suisse)
cmod workshop 2015, Germany, Böblingen

Download Report