PI76514: ANALYZE NOT CAPTURING STATS FOR DATE COLUMN WHEN DATA TYPE IS STORED AS DATE

APAR status

• Closed as fixed if next.

Error description

• When analyze is run against Non-Partitioned tables where a DATE
  column (SNAP_DT) is part of the data fields and the data stored
  in the field has two different formats representing date.
  Analyze only gathers information if the data is stored as
  datetime and ignore date only values.
  See below eg:-
  CREATE EXTERNAL HADOOP TABLE  TESTSCHEMA.TESTTAB
  (
  KEY1 VARCHAR(20),
  KEY2 VARCHAR(20)
  )
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\001'
  LINES TERMINATED BY '\n'
  NULL DEFINED AS ''
  STORED AS TEXTFILE
  LOCATION '/tmp/test1'
  PARTITIONED BY (SNAP_DT DATE STORED AS DATE  NOT NULL,
  RECORD_CODE VARCHAR(30)  NOT NULL );
  INSERT INTO TESTSCHEMA.TESTTAB
  VALUES('TEST_1','KS','2012-01-10','ABC');
  INSERT INTO TESTSCHEMA.TESTTAB
  VALUES('TEST_2','CA','2016-01-11','EFD');
  INSERT INTO TESTSCHEMA.TESTTAB
  VALUES('TEST_3','KS','2014-06-16','ABC');
  INSERT INTO TESTSCHEMA.TESTTAB
  VALUES('TEST_4','MO','2016-01-10','ABC');
  INSERT INTO TESTSCHEMA.TESTTAB
  VALUES('TEST_5','MO','2012-01-22','ZZH');
  INSERT INTO TESTSCHEMA.TESTTAB
  VALUES('TEST_6','KS','2015-01-10','ABC');
  INSERT INTO TESTSCHEMA.TESTTAB
  VALUES('TEST_7','NY','2016-01-30','ABC');
  ANALYZE TABLE TESTSCHEMA.TESTTAB COMPUTE STATISTICS FOR COLUMNS
  KEY1,KEY2,SNAP_DT,RECORD_CODE;
  select colname,high2key,low2key from syscat.columns where
  tabschema='TESTSCHEMA' and tabname='TESTTAB';
  High2Key & low2Key are null.
  

Local fix

• No workaround available
  

Problem summary

• See error description
  

Problem conclusion

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• n/a
  

Fix information

Applicable component levels

• R425 PSY

     UP