Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    138

    Unanswered: help needed with db2reorg api

    AIX5.3 UDB ESE 8.2.5

    I am trying the following C code ..I was able to compile the routine with bldrtn command successfully..but when I call the stored proc, its running successfully without doing any reorg..can some tell me where its going wrong in the C code below?

    #include <sql.h>
    #include <sqludf.h>
    #include <db2ApiDf.h>

    int SQL_API_FN reorg_proc(
    SQLUDF_VARCHAR *tableName)
    {
    SQL_API_RC rc = SQL_RC_OK;
    db2ReorgStruct data;
    struct sqlca sqlca;


    /* initialize data structures */
    data.reorgType = DB2REORG_OBJ_INDEXESALL;
    data.reorgFlags = DB2REORG_OPTION_NONE;
    data.nodeListFlag = DB2_ALL_NODES;
    data.numNodes = 0;
    data.pNodeList = 0;
    data.reorgObject.indexesAllStruct.pTableName = tableName;
    memset(&sqlca, 0x00, sizeof sqlca);

    rc = db2Reorg(db2Version820, &data, &sqlca);
    if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
    memcpy(SQLUDF_STATE, "38RS1", SQLUDF_SQLSTATE_LEN);
    sprintf(SQLUDF_MSGTX, "Error %d returned by db2Reorg.",
    (int)(rc == SQL_RC_OK ? SQLCODE : rc));
    goto cleanup;
    }

    cleanup:
    return SQLZ_DISCONNECT_PROC;
    }


    CREATE PROCEDURE reorgt(IN tableName VARCHAR(256))
    SPECIFIC reorgt
    DYNAMIC RESULT SETS 0
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    LANGUAGE C
    EXTERNAL NAME 'db2_api!reorg_proc'
    FENCED THREADSAFE
    NO EXTERNAL ACTION
    PARAMETER STYLE SQL@

    $ db2 "call reorgt('xxxx.TABNAME')"

    Return Status = 0

    I am following the doc in this link

    http://www-128.ibm.com/developerwork...lze/index.html

  2. #2
    Join Date
    Aug 2004
    Posts
    138
    I just tested the same code in other env and it works fine..may be there is an issue with C compiler or some AIX settings in this env

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    How exactly do you tell that no REORG is being performed?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2004
    Posts
    138
    I am trying reorg only on tables with overflow records and indexes with psuedo-key deleted ids.. after the reorg , they should be cleaned up from the pages..I can see it with reorgchk update statistics on table

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you don't provide any specific details regarding the various statements and their output, it will be pretty hard to say anything...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •