Plan to convert blobs

• env table space has only 8% free of 20 gigs.
• drop table env_scenario_grid;
• how to reclain dropped table?
• rename esg to env_scenario_grid;
• recreate index thusly:

• CREATE index envScenarioGrid_pkey ON ENV_SCENARIO_GRID  (Area_Nm, Scenario_Nm, Param_Nm, Lvl_Nm, Layer_Height, Time_Index)
  TABLESPACE jwars_index;

• ran blobWrite»testEsg on esg table.
• all is good and counts match env_scenario_grid

Blowing Sand 4241 12:00:56 AM
Blowing Snow 4241 12:00:53 AM
Cloud Ceiling Agl 4241 12:00:56 AM
Cloud Ceiling Msl 4241 12:00:56 AM
Density Altitude 4241 12:00:55 AM
Dewpoint Temperature 4241 12:00:54 AM
Fog 4241 12:00:53 AM
Geopotential Height 25446 12:02:08 AM
High Cloud Amount 4241 12:00:55 AM
High Cloud Base Agl 4241 12:00:57 AM
High Cloud Base Msl 4241 12:00:59 AM
High Cloud Top Agl 4241 12:00:57 AM
High Cloud Top Msl 4241 12:00:59 AM
High Cloud Type 4241 12:00:58 AM
High Icing Base 4241 12:00:58 AM
High Icing Intensity 4241 12:00:58 AM
High Icing Top 4241 12:00:58 AM
High Turbulence Intensity 4241 12:00:59 AM
Icing Intensity 2057 12:00:53 AM
Illumination 4241 12:00:59 AM
Intensity Of Predominant Precipitation Type 4241 12:00:59 AM
Low Cloud Amount 4241 12:00:58 AM
Low Cloud Base Agl 4241 12:00:58 AM
Low Cloud Base Msl 4241 12:00:59 AM
Low Cloud Top Agl 4241 12:00:59 AM
Low Cloud Top Msl 4241 12:00:59 AM
Low Cloud Type 4241 12:00:59 AM
Low Icing Base 4241 12:00:59 AM
Low Icing Intensity 4241 12:00:58 AM
Low Icing Top 4241 12:00:59 AM
Low Turbulence Intensity 4241 12:00:59 AM
Mid Cloud Amount 4241 12:00:59 AM
Mid Cloud Base Agl 4241 12:00:58 AM
Mid Cloud Base Msl 4241 12:00:58 AM
Mid Cloud Top Agl 4241 12:00:58 AM
Mid Cloud Top Msl 4241 12:00:59 AM
Mid Cloud Type 4241 12:00:55 AM
Mid Icing Intensity 4241 12:00:54 AM
Mid Turbulence Intensity 4241 12:00:57 AM
Middle Icing Base 4241 12:00:55 AM
Middle Icing Top 4241 12:00:55 AM
Pasquil Stabilty Index 4241 12:00:54 AM
Predominant Type Of Precipitation 4241 12:00:55 AM
Pressure Altitude 4241 12:00:58 AM
Pressure Reduced To Msl 4241 12:00:59 AM
Relative Humidity 25446 12:02:13 AM
Sea State 4241 12:00:59 AM
Snow Depth 4241 12:00:59 AM
Temperature 29687 12:02:27 AM
Terrain Height 4241 12:00:58 AM
Thunderstorm Probability 4241 12:00:58 AM
Total Cloud Cover 4241 12:00:58 AM
Total Precipitation 4241 12:00:59 AM
Turbulence Intensity 2057 12:00:51 AM
Visibility 4241 12:00:58 AM
Wind Chill 4241 12:00:58 AM
Wind Gust Speed 4241 12:00:59 AM
Wind U Component 29687 12:02:19 AM
Wind V Component 29687 12:02:14 AM
364599 1:03:38 AM

• can't use 'for update' with 'select all from someTable where someKey = 'someKey';
• 9.2 did not complain about this but 7.5 did with an ora-01002.
• removed 'for update'.
• could not figure out how to do row rewrite in blobWrite so went with read env_scenario_grid and write to esg.
• still ran out of memory at about 84000 rows when trying to spin through all the param_nms table at once
• did it in 5 or 6 pieces and got the same number of rows in both env_scenario_grid and esg.
• count(*) takes much longer in esg which leads me to believe that it is stored somewhere in an indexed table.

SQL> desc esg;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCENARIO_GRID                                      LONG RAW
 AREA_NM                                   NOT NULL VARCHAR2(50)
 SCENARIO_NM                               NOT NULL VARCHAR2(50)
 PARAM_NM                                  NOT NULL VARCHAR2(50)
 LVL_NM                                    NOT NULL VARCHAR2(80)
 LAYER_HEIGHT                              NOT NULL NUMBER
 TIME_INDEX                                NOT NULL NUMBER
 LONG_RAW_SIZE                                      NUMBER


SQL> select count(*) from  env_scenario_grid;

  COUNT(*)
----------
    364599

SQL> select count (*) from esg;

  COUNT(*)
----------
    364599

• use param_nms to read env_scenario_grid a piece at a time.
• read 364599 12:14:56 AM or 14:56 minutes
• read 364599 12:17:38 AM or 17:38 minutes with “for update' added to sql which means record locking

env_scenario_grid keys and count

'AREA_NM'
    'SEA(15S-15N,90E-150E)' 178959.0
    'SEUS (15N-45N,90W-60W)' 185640.0
    Total 364599.0
'LAYER_HEIGHT'
    0.0 237369.0
    100.0 21205.0
    250.0 21205.0
    500.0 21205.0
    850.0 21205.0
    925.0 21205.0
    1000.0 21205.0
    Total 364599.0
'LVL_NM'
    'Isobaric Surface' 127230.0
    'Mean Sea Level' 4241.0
    'Surface of Earth and Sea' 233128.0
    Total 364599.0
'PARAM_NM'
    'Blowing Sand' 4241.0
    'Blowing Snow' 4241.0
    'Cloud Ceiling Agl' 4241.0
    'Cloud Ceiling Msl' 4241.0
    'Density Altitude' 4241.0
    'Dewpoint Temperature' 4241.0
    'Fog' 4241.0
    'Geopotential Height' 25446.0
    'High Cloud Amount' 4241.0
    'High Cloud Base Agl' 4241.0
    'High Cloud Base Msl' 4241.0
    'High Cloud Top Agl' 4241.0
    'High Cloud Top Msl' 4241.0
    'High Cloud Type' 4241.0
    'High Icing Base' 4241.0
    'High Icing Intensity' 4241.0
    'High Icing Top' 4241.0
    'High Turbulence Intensity' 4241.0
    'Icing Intensity' 2057.0
    'Illumination' 4241.0
    'Intensity Of Predominant Precipitation Type' 4241.0
    'Low Cloud Amount' 4241.0
    'Low Cloud Base Agl' 4241.0
    'Low Cloud Base Msl' 4241.0
    'Low Cloud Top Agl' 4241.0
    'Low Cloud Top Msl' 4241.0
    'Low Cloud Type' 4241.0
    'Low Icing Base' 4241.0
    'Low Icing Intensity' 4241.0
    'Low Icing Top' 4241.0
    'Low Turbulence Intensity' 4241.0
    'Mid Cloud Amount' 4241.0
    'Mid Cloud Base Agl' 4241.0
    'Mid Cloud Base Msl' 4241.0
    'Mid Cloud Top Agl' 4241.0
    'Mid Cloud Top Msl' 4241.0
    'Mid Cloud Type' 4241.0
    'Mid Icing Intensity' 4241.0
    'Mid Turbulence Intensity' 4241.0
    'Middle Icing Base' 4241.0
    'Middle Icing Top' 4241.0
    'Pasquil Stabilty Index' 4241.0
    'Predominant Type Of Precipitation' 4241.0
    'Pressure Altitude' 4241.0
    'Pressure Reduced To Msl' 4241.0
    'Relative Humidity' 25446.0
    'Sea State' 4241.0
    'Snow Depth' 4241.0
    'Temperature' 29687.0
    'Terrain Height' 4241.0
    'Thunderstorm Probability' 4241.0
    'Total Cloud Cover' 4241.0
    'Total Precipitation' 4241.0
    'Turbulence Intensity' 2057.0
    'Visibility' 4241.0
    'Wind Chill' 4241.0
    'Wind Gust Speed' 4241.0
    'Wind U Component' 29687.0
    'Wind V Component' 29687.0
    Total 364599.0
'SCENARIO_NM'
    '01JAN97-30JUN98 [01 AUG 2005]' 185640.0
    '1JAN97-30MAY98' 178959.0
    Total 364599.0

• we can generate esg rows with the correct blob/byteArray in the SCENARIO_GRID column
• do an expdp on the env_scenario_grid table
• check the env table space usage
• it looks like Oracle supports Delete where x = 'something';
• do a count on env_scenario_grid. should be 364599.
• (re)generate current 4241 esg rows lvl_nm = 'Mean Sea Level' (msl).
• expdp them as data only, no table name.
• do a Delete where lvl_nm = 'Mean Sea Level'
• check count on env_scenario_grid.
• do an impdp of the esg rows into env_scenario_grid.
• check count on env_scenario_grid.
• lvl_nm = 'Isobaric Surface' has 127k rows or about 1/3 or 2 gigs.
• lvl_nm = 'Surface of Earth' 233k rows or about 2/3 or 4 gigs.
• read of env_scenario_grid on Dell where lvl_nm = 'Isobaric Surface' blew up with out of memory but froze on 2 later tries.
• do select area_nm, rest of key columns from env_scenario_grid on Dell
• do a Bag on each column to get totals for each key.
• then use keys to read each row one at a time, convert the blob, then write to esg.
• ^^^ WRONG ^^^ then use keys to read each row one at a time, convert the blob, then REWRITE the row.

• retrieved all lvl_nm = 'Mean Sea Level' rows, approximately 4241.
• got the byteArray from 'SCENARIO_GRID' and wrote it to blob1.txt.
• aByteArray := (each at: 'SCENARIO_GRID').
• created the EnvironmentGrid object.
• obj := (ObjectLoader new) loadFromStream: (ReadStream on: aByteArray).
• used ObjectDumper to dump it to blob2.txt.
• ws := WriteStream on: aByteArray. (ObjectDumper new) unload: obj intoStream: ws.
• did fc /b blob1.txt blob2.txt. files are different!
• retrieve takes 36 seconds
• processing takes 3m 26s or 0.414s per each doing write then re-read of blob.
• removing the file i/o does not speed things up on Ayeone VM.
• created 500 esg records on Ayeone VM and then did expdp.
• did impdp on Dell Win10 and ran testEsg which successfully read the new blob!
• next is to move/install Vast 7.5 on Dell Win10
• then run fixEsg and check speed.
• om Dell Win10, retrieval takes 5s, processing takes 24s or .0056 each which is 73 times faster.
• testEsg works too.

1. create table esg with no keys using createEsg.bat in fixEsg directory on ayeone VM
2. the lenovo machine has BLOBs not Long Raws and I had trouble reading them.
3. use sqlplus jwars/password@jas10
4. use OracleServiceJas10 and OracleTnsListener
5. use createEsg.bat to create the table esg.
6. script is fixesg.st.
7. select * from env_scenario_grid where lvl_nm = 'Mean Sea Level';
8. get ora-24374 until use JwOracleDatabaseManager

REM	Description  : Creates Environment Scenario Grid Table
REM	Revision     : 2001 May 15   C Wolcott   Created Script
REM
REM	File Name    : create.table.jwars.envScenarioGrid.sql

REM -- Creates the Environment Scenario Grid table.
CREATE TABLE Env_Scenario_Grid ( 
  SCENARIO_GRID  BLOB, 
  AREA_NM        VARCHAR2 (50)  NOT NULL, 
  SCENARIO_NM    VARCHAR2 (50)  NOT NULL, 
  PARAM_NM       VARCHAR2 (50)  NOT NULL, 
  LVL_NM         VARCHAR2 (80)  NOT NULL, 
  LAYER_HEIGHT   NUMBER        NOT NULL, 
  TIME_INDEX     NUMBER        NOT NULL, 
  LONG_RAW_SIZE  NUMBER, 
  CONSTRAINT envScenarioGrid_pkey PRIMARY KEY (Area_Nm, Scenario_Nm, Param_Nm, Lvl_Nm, Layer_Height, Time_Index) 
      USING INDEX TABLESPACE jwars_index
      STORAGE (INITIAL      10M 
	       NEXT          5M
               MINEXTENTS    1
               MAXEXTENTS  121
               PCTINCREASE   0)) 
 TABLESPACE jwars_environment
   STORAGE (INITIAL        250M
            NEXT           100M
            MINEXTENTS       1
            MAXEXTENTS     512
            PCTINCREASE      0);

REM -- Grant all privileges to the specified role for this table.
GRANT ALL ON Env_Scenario_Grid TO jwarsdb_role;

REM -- Add comments about the table.
COMMENT ON TABLE Env_Scenario_Grid IS
  '';

SQL> select unique lvl_nm from env_scenario_grid;

LVL_NM
--------------------------------------------------------------------------------

Mean Sea Level
Isobaric Surface
Surface of Earth and Sea

SQL> select count(*) from env_scenario_grid where lvl_nm = 'Mean Sea Level';

  COUNT(*)
----------
      4241

SQL> select count(*) from env_scenario_grid where lvl_nm = 'Isobaric Surface';

  COUNT(*)
----------
    127230

SQL> select count(*) from env_scenario_grid where lvl_nm = 'Surface of Earth and
 Sea';

  COUNT(*)
----------
    233128

• use 7.5
• test the below on jwars_preferences first
• use expdp to dump env_scenario_grid.
• get counts of various data types in esg.
• for each of the 3 lvl_nms, do “select all where lvl_nn = 'something'.
• then spin through the result set, and dump the blob using the key as the file name.
• or, then spin through the result set, dump the blob, then use ObjectLoader to retrieve it and rewrite the row.
• or, then spin through the result set, dump the blob, then use ObjectLoader to retrieve it and write the row to a new table.