Topic
  • 8 replies
  • Latest Post - ‏2013-05-01T16:21:03Z by RRL
SystemAdmin
SystemAdmin
374 Posts

Pinned topic Pluglet help - setting values in indexes

‏2013-01-10T21:32:17Z |
Hi,
I'm creating a pluglet to set the values for indexes to the desired values that our DBA's would prefer.
The reason I'm using a pluglet is to make the process more efficient and to ensure consistency.
Specifically, I'd like to set the values on the Partitions table in the properties view of an index (Primary Quantity (PRIQTY), Secondary Quantity (SECQTY), Free Page (FREEPAGE) and PCT Free (PCTFREE).
I can view the values if I do a toString on index.eContents()
e.g., here's a snippet of the toString(): ... primaryQuantity: 720, secondaryQuantity: 720, trackMod: false, freePage: 0, pctFree: 10, ...
Please someone, either point me towards code or documentation on how to set these 4 values. I think an EStructuralFeature may be involved but I'm not sure.
Thanks for the help!!!
Rob
Updated on 2013-01-21T06:24:33Z at 2013-01-21T06:24:33Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Pluglet help - setting values in indexes

    ‏2013-01-10T21:33:40Z  
    Sorry I meant "Partitions tab" not table in the properties view
    Thanks!
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Pluglet help - setting values in indexes

    ‏2013-01-11T10:25:49Z  
    Hi Rob

    If I understand right, you are going to change the index's attributes though Pluglet, right? I have following codes as an example to change the PCTFree, hope it helps.

    //Firstly get the relevant attribute, the String PCTFree should strictly follow the ECore model definition.
    EStructuralFeature feature = index.eClass().getEStructuralFeature("PCTFree");
    //Use set command to update the value. Note: the new value should be integer for PCTFree,otherwise will return exception.
    IDataToolsCommand cmd = CommandFactory.INSTANCE.createSetCommand("", index, feature, 10);
    //At last, execute the command. You can also composite a few commands and execute in one transaction.
    DataToolsPlugin.getDefault().getCommandManager().execute(cmd)

    To get the attribute string, the easy way is to get it using from debugging index.eClass(), the attributes definition are meaningful and easy to pick out. See detail in the attachment.

    regards

    -BM
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Pluglet help - setting values in indexes

    ‏2013-01-11T10:25:50Z  
    Hi Rob

    If I understand right, you are going to change the index's attributes though Pluglet, right? I have following codes as an example to change the PCTFree, hope it helps.

    //Firstly get the relevant attribute, the String PCTFree should strictly follow the ECore model definition.
    EStructuralFeature feature = index.eClass().getEStructuralFeature("PCTFree");
    //Use set command to update the value. Note: the new value should be integer for PCTFree,otherwise will return exception.
    IDataToolsCommand cmd = CommandFactory.INSTANCE.createSetCommand("", index, feature, 10);
    //At last, execute the command. You can also composite a few commands and execute in one transaction.
    DataToolsPlugin.getDefault().getCommandManager().execute(cmd)

    To get the attribute string, the easy way is to get it using from debugging index.eClass(), the attributes definition are meaningful and easy to pick out. See detail in the attachment.

    regards

    -BM
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Pluglet help - setting values in indexes

    ‏2013-01-14T14:38:00Z  
    Hi Rob

    If I understand right, you are going to change the index's attributes though Pluglet, right? I have following codes as an example to change the PCTFree, hope it helps.

    //Firstly get the relevant attribute, the String PCTFree should strictly follow the ECore model definition.
    EStructuralFeature feature = index.eClass().getEStructuralFeature("PCTFree");
    //Use set command to update the value. Note: the new value should be integer for PCTFree,otherwise will return exception.
    IDataToolsCommand cmd = CommandFactory.INSTANCE.createSetCommand("", index, feature, 10);
    //At last, execute the command. You can also composite a few commands and execute in one transaction.
    DataToolsPlugin.getDefault().getCommandManager().execute(cmd)

    To get the attribute string, the easy way is to get it using from debugging index.eClass(), the attributes definition are meaningful and easy to pick out. See detail in the attachment.

    regards

    -BM
    Hi BM,
    Thanks for your note!
    I've tried to follow your suggestion but I'm coming up short.
    Here's a snippet of my code with the output:
    //Display the current value for freePage
    out.println("INDEX_ECONTENTS (PRE CHANGE): " +index.eContents().get(1).toString());

    EStructuralFeature freepage = index.eClass().getEStructuralFeature("freePage");
    IDataToolsCommand cmd = CommandFactory.INSTANCE.createSetCommand("", index, freepage, 20);
    DataToolsPlugin.getDefault().getCommandManager().execute(cmd);

    //Display the updated value for freePage
    out.println("INDEX_ECONTENTS (POST CHANGE): " +index.eContents().get(1).toString());

    CONSOLE OUTPUT:
    INDEX_ECONTENTS (PRE CHANGE): com.ibm.db.models.db2.zSeries.impl.ZSeriesPartitionImpl@30033003 (name: null) (description: null, label: null) (number: 0, useStorageGroup: false, compress: false, GPBCache: CHANGED, primaryQuantity: 48, secondaryQuantity: 48, trackMod: false, freePage: 10, pctFree: 10, limitKey: null, erase: false, inclusive: false)
    INDEX_ECONTENTS (POST CHANGE): com.ibm.db.models.db2.zSeries.impl.ZSeriesPartitionImpl@30033003 (name: null) (description: null, label: null) (number: 0, useStorageGroup: false, compress: false, GPBCache: CHANGED, primaryQuantity: 48, secondaryQuantity: 48, trackMod: false, freePage: 10, pctFree: 10, limitKey: null, erase: false, inclusive: false)

    The freePage value is not being updated.
    I've tried to run the same code but substitute in pctFree for the parameter to the getEStructuralFeature call and I get a null pointer exception.
    I guess what I was really hoping to find was a way of calling getters and setters for the following (primaryQuantity, secondaryQuantity, freePage & pctFree) (i.e., index.setFreePage(20)) but I doubt it's going to be that easy :).
    If you have any more insights into how I could accomplish this task I would greatly appreciate your help!
    Thanks!
    Rob
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Pluglet help - setting values in indexes

    ‏2013-01-17T10:29:16Z  
    Hi BM,
    Thanks for your note!
    I've tried to follow your suggestion but I'm coming up short.
    Here's a snippet of my code with the output:
    //Display the current value for freePage
    out.println("INDEX_ECONTENTS (PRE CHANGE): " +index.eContents().get(1).toString());

    EStructuralFeature freepage = index.eClass().getEStructuralFeature("freePage");
    IDataToolsCommand cmd = CommandFactory.INSTANCE.createSetCommand("", index, freepage, 20);
    DataToolsPlugin.getDefault().getCommandManager().execute(cmd);

    //Display the updated value for freePage
    out.println("INDEX_ECONTENTS (POST CHANGE): " +index.eContents().get(1).toString());

    CONSOLE OUTPUT:
    INDEX_ECONTENTS (PRE CHANGE): com.ibm.db.models.db2.zSeries.impl.ZSeriesPartitionImpl@30033003 (name: null) (description: null, label: null) (number: 0, useStorageGroup: false, compress: false, GPBCache: CHANGED, primaryQuantity: 48, secondaryQuantity: 48, trackMod: false, freePage: 10, pctFree: 10, limitKey: null, erase: false, inclusive: false)
    INDEX_ECONTENTS (POST CHANGE): com.ibm.db.models.db2.zSeries.impl.ZSeriesPartitionImpl@30033003 (name: null) (description: null, label: null) (number: 0, useStorageGroup: false, compress: false, GPBCache: CHANGED, primaryQuantity: 48, secondaryQuantity: 48, trackMod: false, freePage: 10, pctFree: 10, limitKey: null, erase: false, inclusive: false)

    The freePage value is not being updated.
    I've tried to run the same code but substitute in pctFree for the parameter to the getEStructuralFeature call and I get a null pointer exception.
    I guess what I was really hoping to find was a way of calling getters and setters for the following (primaryQuantity, secondaryQuantity, freePage & pctFree) (i.e., index.setFreePage(20)) but I doubt it's going to be that easy :).
    If you have any more insights into how I could accomplish this task I would greatly appreciate your help!
    Thanks!
    Rob
    Hi Rob

    As far as I know, there isn't a getter or setter to set index property simply...

    From your code snippet, I noticed you are using string "freePage" to set freePage value, while I used the following codes to get the features' name:

    Iterator it = index.eClass().getEAllStructuralFeatures().iterator();
    while (it.hasNext()) {
    EStructuralFeature f = (EStructuralFeature) it.next();
    System.out.print(f.getName() +"; ");
    }

    And I got this feature output:
    **********************************************
    eAnnotations; name; dependencies; description; label; comments; extensions; privileges; Schema; clustered; fillFactor; unique; systemGenerated; members; table; ForeignKey; includedMembers; indexType; busPeriodWithoutOverlap; DB2MultidimensionalIndex; PCTFree; minPCTFree; reverseScan; notPartitioned; xmlPattern; asSQLDataType; asSQLDataTypeHashed; systemRequired; pageSplitType; level2PctFree; minPctUsed; compress; collectStats; sampledStats; detailedStats; ignoreInvalidValues; tablespace;
    **********************************************

    I wasn't able to find the string "freePage", so I guess this might be the reason that your code doesn't work.

    From the output, the PCTFree value could be set using this way. For the other properties, if they are not related to the features, maybe we can try some other ways,not sure if your model could be shared, that would be helpful to figure out the new ways.

    regards

    -BM
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Pluglet help - setting values in indexes

    ‏2013-01-17T17:19:45Z  
    Hi Rob

    As far as I know, there isn't a getter or setter to set index property simply...

    From your code snippet, I noticed you are using string "freePage" to set freePage value, while I used the following codes to get the features' name:

    Iterator it = index.eClass().getEAllStructuralFeatures().iterator();
    while (it.hasNext()) {
    EStructuralFeature f = (EStructuralFeature) it.next();
    System.out.print(f.getName() +"; ");
    }

    And I got this feature output:
    **********************************************
    eAnnotations; name; dependencies; description; label; comments; extensions; privileges; Schema; clustered; fillFactor; unique; systemGenerated; members; table; ForeignKey; includedMembers; indexType; busPeriodWithoutOverlap; DB2MultidimensionalIndex; PCTFree; minPCTFree; reverseScan; notPartitioned; xmlPattern; asSQLDataType; asSQLDataTypeHashed; systemRequired; pageSplitType; level2PctFree; minPctUsed; compress; collectStats; sampledStats; detailedStats; ignoreInvalidValues; tablespace;
    **********************************************

    I wasn't able to find the string "freePage", so I guess this might be the reason that your code doesn't work.

    From the output, the PCTFree value could be set using this way. For the other properties, if they are not related to the features, maybe we can try some other ways,not sure if your model could be shared, that would be helpful to figure out the new ways.

    regards

    -BM
    Hi BM,
    I appreciate your patience with me.
    Here's the output of the code snippet you posted to list the features for the index selected.
    ***
    eAnnotations; name; dependencies; description; label; comments; extensions; privileges; Schema; clustered; fillFactor; unique; systemGenerated; members; table; ForeignKey; includedMembers; indexType; busPeriodWithoutOverlap; DB2MultidimensionalIndex; padded; define; close; defer; copy; bufferPoolName; pieceSize; pieceSizeUnits; indexSpaceName; freePage; whereNotNull; erase; xmlPattern; asSQLDataType; compress; partitions; keyExpressions;
    ***
    It's strange that my listing has the feature freePage listed and yours doesn't. Also, I'm not seeing in my listing other items within the Partitions tab for the properties of an Index that I'd like to set (e.g., Primary Quantity, Secondary Quantity nor am I seeing PCT Free which you have). So I'm unsure how to access these features.
    What I was hoping would be a simple task has turned out to have burned too much time at this point (common theme for me and IDA).
    I need a similar type of functionality to update the properties of tablespaces. I'll turn my attention there to see if I glean any additional insights.
    Thanks for your time!
    Rob
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Pluglet help - setting values in indexes

    ‏2013-01-21T06:24:33Z  
    Hi BM,
    I appreciate your patience with me.
    Here's the output of the code snippet you posted to list the features for the index selected.
    ***
    eAnnotations; name; dependencies; description; label; comments; extensions; privileges; Schema; clustered; fillFactor; unique; systemGenerated; members; table; ForeignKey; includedMembers; indexType; busPeriodWithoutOverlap; DB2MultidimensionalIndex; padded; define; close; defer; copy; bufferPoolName; pieceSize; pieceSizeUnits; indexSpaceName; freePage; whereNotNull; erase; xmlPattern; asSQLDataType; compress; partitions; keyExpressions;
    ***
    It's strange that my listing has the feature freePage listed and yours doesn't. Also, I'm not seeing in my listing other items within the Partitions tab for the properties of an Index that I'd like to set (e.g., Primary Quantity, Secondary Quantity nor am I seeing PCT Free which you have). So I'm unsure how to access these features.
    What I was hoping would be a simple task has turned out to have burned too much time at this point (common theme for me and IDA).
    I need a similar type of functionality to update the properties of tablespaces. I'll turn my attention there to see if I glean any additional insights.
    Thanks for your time!
    Rob
    Hi Rob

    I think the differences come from different index instance. I am using org.eclipse.datatools.modelbase.sql.constraints.Index, and the index instance is LUWIndex, so may I know your DB's vendor, or what kind of the instance you got?

    regards
    -BM
  • RRL
    RRL
    6 Posts

    Re: Pluglet help - setting values in indexes

    ‏2013-05-01T16:21:03Z  
    Hi Rob

    I think the differences come from different index instance. I am using org.eclipse.datatools.modelbase.sql.constraints.Index, and the index instance is LUWIndex, so may I know your DB's vendor, or what kind of the instance you got?

    regards
    -BM

    Tagging old post in new forum