IBM Support What's New?

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

Subscribe

You can track all active APARs for this component.

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 number

    PM69527

  • Reported component name

    SPSS STATS SVR

  • Reported component ID

    5725A56SS

  • Reported release

    J00

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-07-24

  • Closed date

    2012-11-28

  • Last modified date

    2012-11-28

  • 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



Document information

More support for: SPSS Statistics
Statistics Server

Software version: 19.0

Reference #: PM69527

Modified date: 2012-11-28