InfoSphere Optim

 View Only
Expand all | Collapse all

Does Optim support archiving tables with columnstore indexes

  • 1.  Does Optim support archiving tables with columnstore indexes

    Posted Mon March 30, 2020 04:04 PM
    Hello,

    When I attempted to save access definitions with columnstore indexes, Optim is displaying errors. Does Optim support archiving tables with columnstore indexes? Example of message:

    SQLText: SELECT * FROM TABLENAME WHERE DATECOLUMN between '10/1/2016' and '11/1/2016'
    ErrTxt1: SQLState=42000 NativeError=35370 Row=1 Col=-1
    ErrMsg1: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cursors are not supported on a
    table which has a clustered columnstore index.
    ErrTxt2: SQLState=42000 NativeError=0 Row=1 Col=-1
    ErrMsg2: [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be
    prepared

    Thank you in advance for your help,

    Diem

    ------------------------------
    Diem Nguyen
    ------------------------------

    #InfoSphereOptim
    #Optim


  • 2.  RE: Does Optim support archiving tables with columnstore indexes

    Posted Tue March 31, 2020 11:08 AM
    Optim uses dynamic SQL in their various stored procs to open cursors against the target tables. Columnstore Indexes do not support cursor processing, so I'm not surprised Optim is throwing this error. I am not sure if Optim excludes these from Archives, but given its reliance on cursor processing I wouldn't be surprised if that is the case. Not sure if there is a workaround, so you may need to get Optim Support involved or see if other users have hit this issue.

    ------------------------------
    Keith Tidball
    Progressive Insurance
    ------------------------------

    Original Message