analytics/informix/extend/spatial.8.22.FC2/debug/dropdeepcopy.sql

----------------------------------------------------------------------------
--
-- Licensed Materials - Property of HCL
-- (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.
--
--  dropdeepcopy.sql
--
--  Background
--  ==========
--  Spatial datatypes (ST_LineString, ST_Polygon, etc) are "multirep",
--  which means that a small value gets stored entirely in the table
--  ("in-row") and a large value gets stored in a smart blob ("out-of-row").
--  A large value always has a small in-row component which contains a
--  reference to the out-of-row smart blob.
--
--
--  The problem
--  ===========
--  When a large out-of-row value is created, it initially consists of
--  a small data structure which contains a memory reference to an
--  out-of-row memory cache.  When, and if, this value actually gets
--  saved in a table, the data in the out-of-row memory cache gets
--  flushed to a smart blob and the small data structure becomes
--  the in-row component of the multirep value.
--
--  A problem can occur when the server needs to copy a multirep value
--  which contains a memory reference.  Version 9.2 of the server doesn't
--  know anything about the out-of-row memory cache, so it doesn't copy it.
--  This means that the multirep value doesn't always persist as long as
--  it should, leading to data corruption.  Usually the Spatial DataBlade
--  detects the error immediately and raises this error:
--
--    (USE21) - Geometry integrity error in <function_name>.
--
--  You may also see errors like these, which may be indicative of
--  corrupt data:
--
--    (USE06) - Unknown ESRI shape library error (-2000) in <function_name>
--    (USE31) - Too few points for geometry type in <function_name>
--    (USE32) - Polygon does not close in <function_name>
--
--
--  The solution
--  ============
--  Starting with version 9.3, the server will execute the Spatial
--  DataBlade's DeepCopy support function for all known scenarios in
--  which it needs to copy a large value.  As the name implies,
--  DeepCopy makes a full copy of the large value, including the
--  portion in the out-of-row memory cache.  This eliminates the
--  data corruption problem by making sure that the entire value
--  persists long enough.
--
--
--  What to do if DeepCopy does not solve your problem
--  ==================================================
--  If your application makes extensive use of SPL, you may encounter
--  a scenario which has not yet been examined, and you might experience
--  data corruption errors.  In this case, please contact
--  IBM Informix Technical Support.
--  They will work with you to isolate your problem.
--  If it can be determined that you have a DeepCopy-related problem, you
--  may be instructed to run this script.  You can do this using
--  dbaccess:
--
--     dbaccess mydb dropdeepcopy.sql
--
--  After running this script, you should stop and restart the
--  server.
--
--
--  Restoring DeepCopy functions
--  ============================
--  If, after running this script, you wish to restore the DeepCopy
--  functions, you may do so by re-registering the Spatial DataBlade
--  using blademgr:
--
--     % blademgr
--     myserver> register spatial.8.11.UC1 mydb
--     Register module spatial.8.11.UC1 into database mydb? [Y/n] y
--     DataBlade module(s) that will be replaced by spatial.8.11.UC1:
--          spatial.8.11.UC1
--     Are these changes ok? [Y/n] y
--     Registering DataBlade module... (may take a while).
--     DataBlade spatial.8.11.UC1 was successfully registered in database mydb.
--     myserver> bye
--
--
--  If you are using server version 9.2
--  ===================================
--  The DeepCopy support function is only used by version 9.3, or later,
--  of the server.  Running this script will have no effect on an older
--  server.  Please see the "Working with Spatial Types in SPL Routines"
--  tech note on the IDN website for information on developing a workaround:
--
--  http://www.informix.com/idn-secure/foundation/Library/spatial_spl.htm
--
----------------------------------------------------------------------------

delete from sysbldobjects
  where obj_signature = 'deepcopy.sql'
    and bld_id like 'spatial%';
delete from sysbldobjects
  where obj_signature = 'alterdeepcopy.sql'
    and bld_id like 'spatial%';
delete from sysbldobjdepends
  where obj_signature = 'deepcopy.sql'
    and bld_id like 'spatial%';
delete from sysbldobjdepends
  where obj_signature = 'alterdeepcopy.sql'
    and bld_id like 'spatial%';
drop function DeepCopy(ST_Geometry);
drop function DeepCopy(ST_Curve);
drop function DeepCopy(ST_MultiCurve);
drop function DeepCopy(ST_Surface);
drop function DeepCopy(ST_MultiSurface);
drop function DeepCopy(ST_Point);
drop function DeepCopy(ST_MultiPoint);
drop function DeepCopy(ST_LineString);
drop function DeepCopy(ST_MultiLineString);
drop function DeepCopy(ST_Polygon);
drop function DeepCopy(ST_MultiPolygon);
drop function DeepCopy(ST_GeomCollection);