Bootstrapping a database

A newly installed KCML system is supplied with a default schema (kconf.xml) which contains sufficient information to bootstrap new databases. To create a database on such a system the following steps should be followed:

• Ensure that the 'system' tablespace in the default kconf.xml points to a valid directory, and that directory is writeable.
• Connect to the 'system' database using KI_CONNECT. This is a virtual database, defined by default in kconf.xml for the express purpose of bootstrapping other databases.
• Create a global tablespace, with CREATE TABLESPACE, to be used as the default tablespace for the new database. It is recomended that this is a FLAT tablespace.
• Now use CREATE DATABASE to create the new database, using the default tablespace created above. The catalog for the new database will be created in the 'system' tablespace, which is why the KCML process must have write permission on the directory to which it points.

There will now be an entry in kconf.xml for the new database.

The 'system' database can now be disconnected, a connection made to the newly created database, further tablespaces added and tables and indices created. The following code fragment gives an outline of the whole procedure.

	DIM hSysConn, hConn, status
	
	REM Connect to the 'system' database.
	CALL KI_ALLOC_CONNECT _KDB_AUTO_HANDLE TO hSysConn, status
	CALL KI_CONNECT "KDB", hsysconn, "system", " ", " " TO status
	
	REM Create the default tablespace.
	'sql(hsysconn, "CREATE TABLESPACE kcc_data '/user3/data/kcc' TYPE FLAT")
	
	REM Create the 'kcc' database.
	'sql(hsysconn, "CREATE DATABASE kcc TYPE KDB DEFAULT TABLESPACE kcc_data")
	
	REM Finished with the 'system' databas
	CALL KI_DISCONNECT hsysconn TO status
	CALL KI_FREE_CONNECT hsysconn TO status
	
	REM Connect to the database we just created.
	CALL KI_ALLOC_CONNECT _KDB_AUTO_HANDLE TO hConn, status
	CALL KI_CONNECT "KDB", hConn, "kcc", " ", " " TO status
	
	REM Create a local tablespace for the new database.
	'sql(hConn, "CREATE LOCAL TABLESPACE kcc_tree_data '/user3/data/kcc/tree' TYPE TREE")
	
	REM Create a table in the new database
	'sql(hConn, "CREATE TABLE kcc_test (pid INTEGER(4), sname VARCHAR(20)) TYPE 7")
	
	REM Create another table, this time in the new tablespace
	'sql(hConn, "CREATE TABLE gb_00_accnt (pid INTEGER(4) NAME 'Identifier', sname VARCHAR(20) NAME 'Surname') TYPE 7, TABLESPACE kcc_tree_data")
	
	REM Put an index on the table.
	'sql(hConn, "CREATE UNIQUE INDEX gb_00_accnt_A01 on gb_00_accnt (pid)")
	
	REM Finished with the database
	CALL KI_DISCONNECT hConn TO status
	CALL KI_FREE_CONNECT hConn TO status
	END
	
	
	DEFSUB 'sql(conn, sql$)
		REM execute SQL on a connection
		LOCAL DIM handle, status
		CALL KI_ALLOC_HANDLE _KDB_TEMP_HANDLE, conn TO handle, status
		IF (status == _KE_SUCCESS)
			CALL KI_PREPARE handle, sql$ TO status
			IF (status == _KE_SUCCESS)
				CALL KI_EXECUTE handle TO status
			END IF
		END IF
		CALL KI_CLOSE handle
		RETURN status
	END SUB