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

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

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



Rate this page:

(0 users)Average rating

Document information


More support for:

SPSS Statistics
Statistics Server

Software version:

19.0

Reference #:

PM69527

Modified date:

2012-11-28

Translate my page

Machine Translation

Content navigation