{ ************************************************************************* }
{                                                                           }
{  Licensed Materials - Property of IBM and/or HCL                                     }
{                                                                           }
{  IBM Informix Dynamic Server                                              }
{  Copyright IBM Corporation 2009, 2015                                     }
{  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.                                     }
{                                                                           }
{   Title:      boot1170.sql                                                }
{                                                                           }
{   Description:                                                            }
{       Bootstrapping script for a 11.70 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.                                                            }
{                                                                           }
{ ************************************************************************* }
create procedure informix.dummyproc_1170()
end procedure;

grant execute on procedure informix.dummyproc_1170 to public as informix;

create dba function informix.cdrcmdfl(informix.integer)
    returns informix.float
    external name '(cdrcmdfl_1)'
    language C;

grant execute on function informix.cdrcmdfl(informix.integer)
    to public as informix;
{ --- SPL debugger: Routines required to handle the session manager --- }
create dba function SYSPROC.DBG_LookupSessionManager(
		OUT host_ip varchar(128),
		OUT port integer)
    returns integer
    external name '(DBG_LookupSessionManager)'
    language C;

grant execute on function SYSPROC.DBG_LookupSessionManager(varchar(128), integer) to public as SYSPROC;

create dba function SYSPROC.DBG_RunSessionManager(
		port integer,
		idle_timeout integer,
		OUT xml_reply blob)
    returns integer
    external name '(DBG_RunSessionManager)'
    language C;

grant execute on function SYSPROC.DBG_RunSessionManager(integer, integer, blob) 
to public as SYSPROC;

create dba function SYSPROC.DBG_EndSessionManager(
		host_ip varchar(128),
		port integer,
		OUT xml_reply blob)
    returns integer
    external name '(DBG_EndSessionManager)'
    language C;

grant execute on function SYSPROC.DBG_EndSessionManager(varchar(128), integer, blob) to public as SYSPROC;

create dba function SYSPROC.DBG_PingSessionManager(
		host_ip varchar(128),
		port integer,
		OUT xml_reply blob)
    returns integer
    external name '(DBG_PingSessionManager)'
    language C;

grant execute on function SYSPROC.DBG_PingSessionManager(varchar(128), integer, blob) to public as SYSPROC;

create dba function SYSPROC.DBG_InitializeClient(
		host_ip varchar(128),
		port integer,
		xml_request blob,
		OUT xml_reply blob)
    returns integer
    external name '(DBG_InitializeClient)'
    language C;

grant execute on function SYSPROC.DBG_InitializeClient(varchar(128), integer, blob, blob) to public as SYSPROC;

create dba function SYSPROC.DBG_TerminateClient(
		host_ip varchar(128),
		port integer,
		xml_request blob,
		OUT xml_reply blob)
    returns integer
    external name '(DBG_TerminateClient)'
    language C;

grant execute on function SYSPROC.DBG_TerminateClient(varchar(128), integer, blob, blob) to public as SYSPROC;

create dba function SYSPROC.DBG_SendClientRequests(
		host_ip varchar(128),
		port integer,
		xml_request blob,
		OUT xml_reply blob)
    returns integer
    external name '(DBG_SendClientRequests)'
    language C;

grant execute on function SYSPROC.DBG_SendClientRequests(varchar(128), integer, blob, blob) to public as SYSPROC;

create dba function SYSPROC.DBG_SendClientCommands(
		host_ip varchar(128),
		port integer,
		xml_request blob,
		xml_data blob,
		bin_data blob,
		OUT xml_reply blob)
    returns integer
    external name '(DBG_SendClientCommands)'
    language C;

grant execute on function SYSPROC.DBG_SendClientCommands(varchar(128), integer, blob, blob, blob, blob) to public as SYSPROC;

create dba function SYSPROC.DBG_RecvClientReports(
		host_ip varchar(128),
		port integer,
		xml_request blob,
		OUT xml_reply blob,
		OUT xml_data blob,
		OUT bin_data blob)
    returns integer
    external name '(DBG_RecvClientReports)'
    language C;

grant execute on function SYSPROC.DBG_RecvClientReports(varchar(128), integer, blob, blob, blob, blob) to public as SYSPROC;


create dba function informix.filetoblob(informix.lvarchar, char(6), char(128), char(128)) 
	returns informix.blob
        with (handlesnulls)
	external name '(blob_from_file_colspec)'
	language c variant;

grant execute on function informix.filetoblob(informix.lvarchar, char(6), char(128), char(128)) to public as informix ;

create dba function informix.filetoclob(informix.lvarchar, char(6), char(128), char(128)) 
	returns informix.clob
        with (handlesnulls)
	external name '(blob_from_file_colspec)'
	language c variant;

grant execute on function informix.filetoclob(informix.lvarchar, char(6), char(128), char(128)) to public as informix ;

create dba function informix.locopy(informix.blob, char(128), char(128)) 
	returns informix.blob
        with (handlesnulls)
	external name '(blob_copy_colspec)'
	language c variant;

grant execute on function informix.locopy(informix.blob, char(128), char(128)) to public as informix ;

create dba function informix.locopy(informix.clob, char(128), char(128)) 
	returns informix.clob
        with (handlesnulls)
	external name '(blob_copy_colspec)'
	language c variant;

grant execute on function informix.locopy(informix.clob, char(128), char(128)) to public as informix ;


create dba procedure informix.ifx_grid_connect(informix.lvarchar)
  with (handlesnulls,parallelizable)
  external name '(grid_connect1)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_connect(informix.lvarchar)
    to public as informix;

create dba procedure
  informix.ifx_grid_connect(
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls,parallelizable)
  external name '(grid_connect2)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_connect(
        informix.lvarchar,
        informix.lvarchar)
    to public as informix;


create dba procedure
  informix.ifx_grid_connect(
    informix.lvarchar,
    informix.lvarchar,
    informix.integer)
  with (handlesnulls,parallelizable)
  external name '(grid_connect3)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_connect(
        informix.lvarchar,
        informix.lvarchar,
        informix.integer)
    to public as informix;

create dba procedure
  informix.ifx_grid_connect(
    informix.lvarchar,
    informix.integer)
  with (handlesnulls,parallelizable)
  external name '(grid_connect4)'
  language C
  end procedure;

grant execute on procedure  informix.ifx_grid_connect(
        informix.lvarchar,
        informix.integer)
    to public as informix;



create dba procedure
  informix.ifx_grid_disconnect()
  with (handlesnulls, parallelizable)
  external name '(grid_disconnect)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_disconnect()
    to public as informix;

create dba procedure 
  informix.ifx_grid_procedure(
        informix.lvarchar,
        informix.lvarchar)
  with (handlesnulls)
  external name '(grid_procedure2)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_procedure(
        informix.lvarchar,
        informix.lvarchar)
    to public as informix;

create dba procedure
  informix.ifx_grid_procedure(
        informix.lvarchar,
        informix.lvarchar,
        informix.lvarchar)
  with (handlesnulls)
  external name '(grid_procedure3)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_procedure(
        informix.lvarchar,
        informix.lvarchar,
        informix.lvarchar)
    to public as informix;

create dba procedure
  informix.ifx_grid_execute(
        informix.lvarchar,
        informix.lvarchar)
  with (handlesnulls)
  external name '(grid_execute2)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_execute(
        informix.lvarchar,
        informix.lvarchar)
    to public as informix;

create dba procedure
  informix.ifx_grid_execute(
        informix.lvarchar,
        informix.lvarchar,
        informix.lvarchar)
  with (handlesnulls)
  external name '(grid_execute3)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_execute(
        informix.lvarchar,
        informix.lvarchar,
        informix.lvarchar)
    to public as informix;



create dba function informix.ifx_grid_function(
        informix.lvarchar,
        informix.lvarchar)
  returns lvarchar(30000)
  with (handlesnulls)
  external name '(grid_function2)'
  language C;

grant execute on function informix.ifx_grid_function(
        informix.lvarchar,
        informix.lvarchar)
    to public as informix;

create dba function informix.ifx_grid_function(
        informix.lvarchar,
        informix.lvarchar,
        informix.lvarchar)
  returns lvarchar(30000)
  with (handlesnulls)
  external name '(grid_function3)'
  language C;

grant execute on function informix.ifx_grid_function(
        informix.lvarchar,
        informix.lvarchar,
        informix.lvarchar)
    to public as informix;



create dba procedure informix.ifx_grid_executecmd()
    with (handlesnulls)
    external name '(grid_executeCmd)'
    language C
    end procedure;

grant execute on procedure 
    informix.ifx_grid_executecmd()
    to public as informix;

create dba function informix.ifx_get_erstate()
    returns integer
    external name '(ifx_get_erstate)'
    language C;

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

create dba procedure informix.ifx_set_erstate( informix.integer)
    external name '(ifx_set_erstate1)'
    language C
    end procedure;

grant execute on procedure informix.ifx_set_erstate(informix.integer)
    to public as informix;

create dba procedure informix.ifx_set_erstate( informix.lvarchar)
    external name '(ifx_set_erstate2)'
    language C
    end procedure;

grant execute on procedure informix.ifx_set_erstate(informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_redo()
  external name '(grid_redo0)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_redo()
    to public as informix;

create dba procedure informix.ifx_grid_redo(
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_redo1)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_redo(
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_redo2)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_redo3)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_redo4)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_redo5)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_redo6)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_redo(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_purge()
  external name '(grid_purge0)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_purge()
    to public as informix;

create dba procedure informix.ifx_grid_purge(
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_purge1)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_purge(
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_purge2)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_purge3)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_purge4)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_purge5)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

create dba procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
  with (handlesnulls)
  external name '(grid_purge6)'
  language C
  end procedure;

grant execute on procedure informix.ifx_grid_purge(
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar,
    informix.lvarchar)
    to public as informix;

{ --- Create new sysfdist  procedure  }
create dba procedure informix.sysfdist (table_id int, column_no int)
   returning int, int, datetime year to fraction (5), stat;

   define v_tabauth      char(8);
   define v_colauth      char(3);
   define is_allowed     int;
   define search_columns int;
   define v_colno        smallint;
   define v_fragid       int;
   define v_seqno        int;
   define v_constr_time  datetime year to fraction(5);
   define v_mode         char(1);
   define v_encdist      stat;
   define v_owner        char(8);
   define user           procedure;

-- First verify that the current user has select privileges on this column

   let is_allowed = 0;
   let search_columns = 0;

   if user = 'informix' then
      let is_allowed = 1;
   else
      -- Check sysusers to see if the usertype is 'D', ie., the
      -- current user has dba privileges and may see any columns.
         select usertype
            into v_mode
            from informix.sysusers
            where username = user;
         if v_mode = 'D' then  
            let is_allowed = 1;
         else
            -- See if the user owns the table, and therefore can see the columns.
            select owner
               into v_owner
               from informix.systables
               where tabid = table_id;
            if v_owner = user then
               let is_allowed = 1;
         end if
      end if
   end if

   if is_allowed = 0 then
      foreach
         select tabauth
            into v_tabauth
            from informix.systabauth
            where tabid = table_id and
                  (grantee = user or
                   grantee = 'public')
         if substr(v_tabauth, 1, 1) = 's' or substr(v_tabauth, 1, 1) = 'S' then
            let is_allowed = 1;
            exit foreach;
         elif substr(v_tabauth, 3, 1) = '*' then
            let search_columns = 1;
         end if
      end foreach
   end if

-- Search syscolauth only if user does not have select
-- privileges on all columns.  If the user has no select
-- privileges on any column, then we need search no further.

   if is_allowed = 0 and search_columns = 1 then
      foreach
         select colauth
            into v_colauth
            from informix.syscolauth
            where tabid = table_id and
                  colno = column_no and
                  (grantee = user or
                   grantee = 'public')
         if substr(v_colauth, 1, 1) = 's' or substr(v_colauth, 1, 1) = 'S' then
            let is_allowed = 1;
            exit foreach;
         end if
      end foreach
   end if

-- Return with no rows found if not allowed to select from
-- the column designated by (tabid,colno).

   if is_allowed = 0 then
      raise exception -272;
   end if

-- Now find the distribution rows
   foreach
      select  fragid, seqno, constr_time, encdist
         into v_fragid, v_seqno, v_constr_time, v_encdist
         from informix.sysfragdist
         where tabid = table_id and
               colno = column_no
         order by fragid
      return v_fragid, v_seqno, v_constr_time, v_encdist
         with resume;
   end foreach


-- Engine will return 100 to user

end procedure;
   
grant execute on informix.sysfdist to public as informix;