PM69527: POSTGRESQL DATABASE NEEDS SPSS_TEMP REFERENCES IN IBM SPSS STATISTICS SAVE TRANSLATE SYNTAX COMMANDS TO BE QUOTED

APAR status

• Closed as program error.

Error description

• You are using IBM SPSS Statistics to save data via ODBC to a
  PostGreSQL database.  The File->Export to Database dialog box
  creates something like the following SAVE TRANSLATE command for
  a simple "create new table":
  
  SAVE TRANSLATE /TYPE=ODBC
  
  /CONNECT='DSN=PostgreSQL30;DATABASE=postgres;SERVER=localhost;PO
  RT=5432;UID=postgres;PWD=?c,'+
  
  'N!R-^/!-/!-/$;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIn
  dex=0;ShowOidColumn='+
  
  '0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;So
  cket=4096;UnknownSizes='+
  
  '0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;
  Optimizer=0;Ksqo='+
  
  '1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0
  ;BoolsAsChar=1;Parse='+
  
  '0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;U
  pdatableCursors='+
  
  '1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=
  0;UseServerSidePrepare='+
      '0;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1'
    /ENCRYPTED
    /MISSING=IGNORE
    /SQL='CREATE TABLE EmployeeData (id float8 , gender varchar
  (1), bdate date , educ float8 , '+
      'jobcat float8 , salary float8 , salbegin float8 , jobtime
  float8 , prevexp float8 , minority '+
      'float8 )'
    /REPLACE
    /TABLE='SPSS_TEMP'
    /KEEP=id, gender, bdate, educ, jobcat, salary, salbegin,
  jobtime, prevexp, minority
    /SQL='INSERT INTO EmployeeData (id, gender, bdate, educ,
  jobcat, salary, salbegin, jobtime, '+
      'prevexp, minority) SELECT id, gender, bdate, educ, jobcat,
  salary, salbegin, jobtime, '+
      'prevexp, minority FROM SPSS_TEMP'
    /SQL='DROP TABLE SPSS_TEMP'.
  
  When executed, however, this command throws the following
  errors:
  
  >Error # 6492
  >The ODBC subsystem has issued an error which prevents the
  processing of SAVE
  >TRANSLATE ODBC request.
  >Execution of this command stops.
  >ERROR: relation "spss_temp" does not exist;
  Error while executing the query
  
  >Error # 6492
  >The ODBC subsystem has issued an error which prevents the
  processing of SAVE
  >TRANSLATE ODBC request.
  >ERROR: table "spss_temp" does not exist;
  Error while executing the query
  

Local fix

• It appears that, unlike most other common databases, the
  PostGreSQL requires that the references to the SPSS_TEMP table
  -- once it's created -- be enclosed in additional quotes.  The
  workaround is to paste the SAVE TRANSLATE command and then
  manually edit the last two /SQL subcommands so that both
  references to SPSS_TEMP become "SPSS_TEMP":
  
    /SQL='INSERT INTO EmployeeData (id, gender, bdate, educ,
  jobcat, salary, salbegin, jobtime, '+
      'prevexp, minority) SELECT id, gender, bdate, educ, jobcat,
  salary, salbegin, jobtime, '+
      'prevexp, minority FROM "SPSS_TEMP"'
    /SQL='DROP TABLE "SPSS_TEMP"'.
  
  This issue will be addressed in a future FixPack or release of
  IBM SPSS
  Statistics.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Anyone using IBM SPSS Statistics to save data via ODBC to a  *
  * PostGreSQL database via the SAVE TRANSLATE command.          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * You are using IBM SPSS Statistics to save data via ODBC to a *
  * PostGreSQL database.  The File->Export to Database dialog    *
  * box creates something like the following SAVE TRANSLATE      *
  * command for a simple "create new table":                     *
  *                                                              *
  * SAVE TRANSLATE /TYPE=ODBC                                    *
  * /CONNECT='DSN=PostgreSQL30;DATABASE=postgres;SERVER=localhos *
  * t;PORT=5432;UID=postgres;PWD=?c,'+                           *
  * 'N!R-^/!-/!-/$;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeO *
  * idIndex=0;ShowOidColumn='+                                   *
  * '0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=10 *
  * 0;Socket=4096;UnknownSizes='+                                *
  * '0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLo *
  * g=0;Optimizer=0;Ksqo='+                                      *
  * '1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarch *
  * ar=0;BoolsAsChar=1;Parse='+                                  *
  * '0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion *
  * =1;UpdatableCursors='+                                       *
  * '1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBin *
  * ary=0;UseServerSidePrepare='+                                *
  * '0;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1'            *
  *   /ENCRYPTED                                                 *
  *   /MISSING=IGNORE                                            *
  *   /SQL='CREATE TABLE EmployeeData (id float8 , gender        *
  * varchar(1), bdate date , educ float8 , '+                    *
  *     'jobcat float8 , salary float8 , salbegin float8 ,       *
  * jobtime float8 , prevexp float8 , minority '+                *
  *     'float8 )'                                               *
  *   /REPLACE                                                   *
  *   /TABLE='SPSS_TEMP'                                         *
  *   /KEEP=id, gender, bdate, educ, jobcat, salary,             *
  * salbegin,jobtime, prevexp, minority                          *
  *   /SQL='INSERT INTO EmployeeData (id, gender, bdate,         *
  * educ,jobcat, salary, salbegin, jobtime, '+                   *
  *     'prevexp, minority) SELECT id, gender, bdate, educ,      *
  * jobcat,salary, salbegin, jobtime, '+                         *
  *     'prevexp, minority FROM SPSS_TEMP'                       *
  *   /SQL='DROP TABLE SPSS_TEMP'.                               *
  *                                                              *
  * When executed, however, this command throws the following    *
  * errors:                                                      *
  *                                                              *
  * >Error # 6492                                                *
  * >The ODBC subsystem has issued an error which prevents the   *
  * processing of SAVE                                           *
  * >TRANSLATE ODBC request.                                     *
  * >Execution of this command stops.                            *
  * >ERROR: relation "spss_temp" does not exist;Error while      *
  * executing the query                                          *
  *                                                              *
  * >Error # 6492                                                *
  * >The ODBC subsystem has issued an error which prevents the   *
  * processing of SAVE                                           *
  * >TRANSLATE ODBC request.                                     *
  * >ERROR: table "spss_temp" does not exist;Error while         *
  * executing the query                                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply the IBM SPSS Statistics 20.0 Fix Pack 2 if using       *
  * version 20.0.  This issue will also be addressed in the next *
  * Fix Pack release of Statistics 21.0.                         *
  *                                                              *
  * Until you can apply the relevant Fix Pack(s), you can        *
  * workaround the issue by pasting and editing the command      *
  * syntax before execution.  It appears that, unlike most other *
  * common databases, the PostGreSQL requires that the           *
  * references to the SPSS_TEMP table -- once it's created -- be *
  * enclosed in additional quotes.  The workaround is to paste   *
  * the SAVE TRANSLATE command and then manually edit the last   *
  * two /SQL subcommands so that both                            *
  * references to SPSS_TEMP become "SPSS_TEMP":                  *
  *                                                              *
  *   /SQL='INSERT INTO EmployeeData (id, gender, bdate, educ,   *
  * jobcat, salary, salbegin, jobtime, '+                        *
  *     'prevexp, minority) SELECT id, gender, bdate, educ,      *
  * jobcat, salary, salbegin, jobtime, '+                        *
  *     'prevexp, minority FROM "SPSS_TEMP"'                     *
  *   /SQL='DROP TABLE "SPSS_TEMP"'.                             *
  ****************************************************************
  

Problem conclusion

• This issue is addressed in the IBM SPSS Statistics 20.0 Fix Pack
  2 and in the next Fix Pack release of Statistics 21.0.
  

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:

Fix information

• Fixed component name

  SPSS STATS SVR

• Fixed component ID

  5725A56SS

Applicable component levels

• RK00 PSY

     UP