analytics/informix/etc/boot920.sql

{ ************************************************************************* }
{                                                                           }
{  Licensed Materials - Property of IBM and/or HCL                                     }
{                                                                           }
{  IBM Informix Dynamic Server                                              }
{  (c) Copyright IBM Corporation 1996, 2004 All rights reserved.            }
{  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.             }
{                                                                           }
{ ************************************************************************* }
{                                                                           }
{   Title:      boot920.sql                                                 }
{                                                                           }
{   Description:                                                            }
{       Bootstrapping script for a 9.20 database                            }
{                                                                           }
{ ************************************************************************* }
{                                                                           }
{  ** IMPORTANT - PLEASE READ                                               } 
{                                                                           }
{     All types and routines referenced in this file must be prefixed       }
{     with the user name "informix". E.g., use "informix.boolean"           }
{     and not "boolean"                                                     }
{									    }
{     Also, please follow the formatting conventions!!!                     }
{                                                                           }
{     Please ensure that the identifiers that you choose are <= 18          }
{     characters in length. Otherwise the changes would affect database     }
{     reversion.                                                            }
{                                                                           }
{ ************************************************************************* }

--			MORE IMPORTANT: MUST READ 

-- DO NOT ADD ANYTHING IN THIS FILE. ADDING STUFF IN THIS FILE MAY CAUSE 
-- FAILURE IN CONVERSION/REVERSION PROCESS. USE boot920a.sql. 

---Define the hash access method.
---Do not grant execute to public for these functions as
---they are special-cased within the engine with the exception
---of ifx_sha_settrace().

create dba function informix.sha_create(informix.pointer)
	returns int
	external name '(sha_create)'
	language C;

create dba function informix.sha_open(informix.pointer)
	returns int
	external name '(sha_open)'
	language C;

create dba function informix.sha_close(informix.pointer)
	returns int
	external name '(sha_close)'
	language C;

create dba function informix.sha_insert(informix.pointer,
	    informix.pointer, informix.pointer)
	returns int
	external name '(sha_insert)'
	language C;

create dba function informix.sha_update(informix.pointer,
	    integer, informix.pointer, informix.pointer,
	    informix.pointer)
	returns int
	external name '(sha_update)'
	language C;

create dba function informix.sha_delete(informix.pointer, integer)
	returns int
	external name '(sha_delete)'
	language C;

create dba function informix.sha_scancost(informix.pointer,
	    informix.pointer)
	returns float
	external name '(sha_scancost)'
	language C;

create dba function informix.sha_beginscan(informix.pointer)
	returns int
	external name '(sha_beginscan)'
	language C;

create dba function informix.sha_rescan(informix.pointer)
	returns int
	external name '(sha_rescan)'
	language C;

create dba function informix.sha_getnext(informix.pointer,
	    informix.pointer, informix.pointer)
	returns int
	external name '(sha_getnext)'
	language C;

create dba function informix.sha_getbyid(informix.pointer,
	    informix.pointer, integer)
	returns int
	external name '(sha_getbyid)'
	language C;

---Create function to enable trace. 
create dba function informix.ifx_sha_settrace(integer, lvarchar)
	returns integer
	external name '(ifx_sha_settrace)'
	language C;

---Create the access method
create primary access_method informix.hash(
    am_create=informix.sha_create,
    am_open=informix.sha_open,
    am_close=informix.sha_close,
    am_insert=informix.sha_insert,
    am_update=informix.sha_update,
    am_delete=informix.sha_delete,
    am_scancost=informix.sha_scancost,
    am_beginscan=informix.sha_beginscan,
    am_rescan=informix.sha_rescan,
    am_getnext=informix.sha_getnext,
    am_getbyid=informix.sha_getbyid,
    am_rowids,
    am_readwrite,
    am_sptype='D');


--- Alter rtree functions to make them parallelizable
alter function informix.rlt_open(pointer)
with (add parallelizable);

alter function informix.rlt_close(pointer)
with (add parallelizable);

alter function informix.rlt_insert(pointer, pointer, pointer) 
with (add parallelizable);

alter function informix.rlt_delete(pointer, pointer, pointer) 
with (add parallelizable);

{Not yet for rtree update}
{alter function informix.rlt_update}
{(pointer, pointer, pointer, pointer, pointer) }
{with (add parallelizable);}

alter function informix.rlt_beginscan(pointer)
with (add parallelizable);

alter function informix.rlt_endscan(pointer)
with (add parallelizable);

alter function informix.rlt_rescan(pointer)
with (add parallelizable);

alter function informix.rlt_getnext(pointer, pointer, pointer) 
with (add parallelizable);


---Define rtree support function for oncheck
create function informix.rlt_check(pointer, integer)
	returns integer
	external name '(rlt_check)'
	language C;

alter access_method informix.rtree add am_check = informix.rlt_check;

---Add new collectionsend() to handle all collection types
create dba function informix.collectionsend(collection)
        returns informix.sendrecv
        external name '(collectionsend)'
        language C not variant;

grant execute on function informix.collectionsend(collection)
        to public as informix ;

-- create a function to enable unloading of a module.
create dba function informix.unloading_module(varchar(255), varchar(255)) 
        returns int
        external name '' language C;
 
create procedure informix.ifx_unload_module (modulename varchar(255), 
        languagename varchar(255)) returns int
     
     define retvalue int;
     let retvalue = unloading_module(modulename, languagename);
     if retvalue = 0 then
         return 0;
     end if;
     if retvalue = -1 then
         raise exception -9721;
     else
         raise exception -9720;
     end if;
end procedure;


create opaque type informix.stat
(
        internallength=variable,
        alignment = 8,
        maxlen = 272,
        cannothash
);


create dba function informix.statout(informix.stat) 
	returns informix.lvarchar
	external name '(showstat)'
	language C not variant;

grant execute on function informix.statout(informix.stat) to
 public as informix ;

create explicit cast (informix.stat as informix.lvarchar
	 with informix.statout);


create dba function informix.assign(informix.stat)
       returns informix.stat
       external name '(stat_assign)'
       language C not variant;

create dba function informix.destroy(informix.stat)
       returns informix.stat
       external name '(stat_destroy)'
       language C not variant;

create dba function informix.send(informix.stat)
       returns informix.sendrecv
       external name '(stat_send)'
       language C not variant;

grant execute on function informix.send(informix.stat) to
 public as informix ; 

create explicit cast (informix.stat as informix.sendrecv with informix.send);

-- add collectionimport and collectionexport functions
create dba function informix.collectionimport(informix.impexp) returns set
        external name '(collectionimport)' language C not variant;

grant execute on function informix.collectionimport(informix.impexp)
	to public as informix;

create implicit cast (informix.impexp as collection with
	informix.collectionimport);
create implicit cast (informix.impexp as set with
	informix.collectionimport);
create implicit cast (informix.impexp as multiset with
	informix.collectionimport);
create implicit cast (informix.impexp as list with
	informix.collectionimport);

create dba function informix.collectionexport(collection) returns informix.impexp
        external name '(collectionexport)' language C not variant;
create dba function informix.collectionexport(set) returns informix.impexp
        external name '(collectionexport)' language C not variant;
create dba function informix.collectionexport(multiset) returns informix.impexp
        external name '(collectionexport)' language C not variant;
create dba function informix.collectionexport(list) returns informix.impexp
        external name '(collectionexport)' language C not variant;

grant execute on function informix.collectionexport(collection)
	to public as informix;
grant execute on function informix.collectionexport(set)
	to public as informix;
grant execute on function informix.collectionexport(multiset)
	to public as informix;
grant execute on function informix.collectionexport(list)
	to public as informix;

create implicit cast (collection as informix.impexp with
	informix.collectionexport);
create implicit cast (set as informix.impexp with
	informix.collectionexport);
create implicit cast (multiset as informix.impexp with
	informix.collectionexport);
create implicit cast (list as informix.impexp with
	informix.collectionexport);

grant usage on language spl to public;


-- fastpath entry points to support client sblob range locking

create dba function informix.ifx_lo_lock(integer, int8, integer, int8, integer)
	returns integer
	external name '(sq_lo_lock)'
	language c;
grant execute on function informix.ifx_lo_lock(integer, int8, integer, int8, integer) to public as informix;

create dba function informix.ifx_lo_unlock(integer, int8, integer, int8)
	returns integer
	external name '(sq_lo_unlock)'
	language c;
grant execute on function informix.ifx_lo_unlock(integer, int8, integer, int8) to public as informix;

-- cast from byte to blob 

create function informix.bytetoblob(references byte)
	returns informix.blob
	external name '(bytetoblob)'
	language c not variant;

grant execute on function informix.bytetoblob(references byte) 
	to public as informix;

create explicit cast (byte as informix.blob with informix.bytetoblob);

create function informix.bytetoblob(references byte, char(18), char(18))
	returns informix.blob
	external name '(bytetoblob_colspec)'
	language c not variant;

grant execute on function informix.bytetoblob(references byte, char(18), char(18)) 
	to public as informix;

-- cast from text to clob

create function informix.texttoclob(references text)
	returns informix.clob
	external name '(bytetoblob)'
	language c not variant;

grant execute on function informix.texttoclob(references text)
	to public as informix;

create explicit cast (text as informix.clob with informix.texttoclob);

create function informix.texttoclob(references text, char(18), char(18))
	returns informix.clob
	external name '(bytetoblob_colspec)'
	language c not variant;

grant execute on function informix.texttoclob(references text, char(18), char(18))
	to public as informix; 

create dba function informix.rtnparamtypes_send(informix.rtnparamtypes)
returning informix.sendrecv
external name '(rtnparamtypes_send)' language C
not variant;

grant execute on function informix.rtnparamtypes_send(informix.rtnparamtypes) to
 public as informix ;

create implicit cast (informix.rtnparamtypes as informix.sendrecv
                with informix.rtnparamtypes_send);

---
---	Register builtin java UDRs for Krakatoa
---

create dba procedure sqlj.install_jar(varchar(255), varchar(255))
external name 'informix.jvp.dbapplet.impl.JarHandler.installJar(java.lang.String, java.lang.String)' language java end procedure;

grant execute on procedure sqlj.install_jar(varchar(255), varchar(255))
	to public as sqlj;

create dba procedure sqlj.remove_jar(varchar(255))
external name 'informix.jvp.dbapplet.impl.JarHandler.removeJar(java.lang.String) ' language java end procedure;

grant execute on procedure sqlj.remove_jar(varchar(255))
	to public as sqlj;

create dba procedure sqlj.replace_jar(varchar(255), varchar(255))
external name 'informix.jvp.dbapplet.impl.JarHandler.replaceJar(java.lang.String, java.lang.String)' language java end procedure;

grant execute on procedure sqlj.replace_jar(varchar(255), varchar(255))
	to public as sqlj;

create dba procedure sqlj.install_jar_deploy(varchar(255), varchar(255))
external name 'informix.jvp.dbapplet.impl.JarHandler.installJarDeploy(java.lang.String, java.lang.String)' language java end procedure;

grant execute on procedure sqlj.install_jar_deploy(varchar(255), 
	varchar(255)) to public as sqlj;

create dba procedure sqlj.remove_jar_undeploy(varchar(255))
external name 'informix.jvp.dbapplet.impl.JarHandler.removeJarUndeploy(java.lang.String)' language java end procedure;

grant execute on procedure sqlj.remove_jar_undeploy(varchar(255))
	to public as sqlj;

create dba procedure sqlj.alter_java_path(varchar(255), lvarchar)
external name 'informix.jvp.dbapplet.impl.JarHandler.alterJavaPath(java.lang.String, java.lang.String)' language java end procedure;

grant execute on procedure sqlj.alter_java_path(varchar(255), 
	lvarchar) to public as sqlj;

create dba procedure sqlj.setUDTExtName(varchar(255), varchar(255))
external name 'informix.jvp.dbapplet.impl.JarHandler.setUDTExternalName(java.lang.String, java.lang.String)' language java end procedure;

grant execute on procedure sqlj.setUDTExtName(varchar(255), 
	varchar(255)) to public as sqlj;

create dba procedure sqlj.unsetUDTExtName(udtSQLName varchar(255))
external name 'informix.jvp.dbapplet.impl.JarHandler.unsetUDTExternalName(java.lang.String)' language java end procedure;

grant execute on procedure sqlj.unsetUDTExtName(varchar(255)) 
	to public as sqlj;

---
---	end java UDRs
---

 
create dba function informix.ifx_update_extern(varchar(255), varchar(255),
        varchar(255) )
        returns  int
        external name '' language C;
 
grant execute on function informix.ifx_update_extern(varchar(255), varchar(255),varchar(255))
              to public as informix ;

create dba procedure informix.ifx_replace_module (oldmodulename varchar(255),
        newmodulename varchar(255),
        languagename varchar(255)) returns int
 
     define retvalue int;
     let retvalue = ifx_invalid_module(oldmodulename,
                          languagename);

     if retvalue = 0 then
         let retvalue = ifx_update_extern( oldmodulename, newmodulename, languagename);
         update informix.sysprocedures
         set externalname = ifx_new_external(newmodulename, externalname,
                              languagename)
         where ifx_match_external(oldmodulename, externalname) = 1;
         let retvalue = ifx_load_internal(newmodulename, languagename);
     end if;
 
     if retvalue = 0 then
         return 0;
     else
         raise exception -9720;
     end if;
 
end procedure;

grant execute on procedure informix.ifx_replace_module (varchar(255),varchar(255),
     varchar(255)) to public as informix;

--- Alter blob/clob routines to allow handling nulls
alter function informix.filetoblob(informix.lvarchar, char(6), char(18), char(18))
with (add handlesnulls);

alter function informix.filetoclob(informix.lvarchar, char(6), char(18), char(18))
with (add handlesnulls);

alter function informix.locopy(informix.blob, char(18), char(18))
with (add handlesnulls);

-- Define conversion and reversion functions for rtree index partitions
create dba function informix.rlt_convert(int)
    returns int
    external name '(rlt_convert)'
    language C;

grant execute on function informix.rlt_convert(int) to public as informix ;

create dba function informix.rlt_revert(int)
    returns int
    external name '(rlt_revert)'
    language C;

grant execute on function informix.rlt_revert(int) to public as informix ;