Skip to main content

Error on setting the Storage Policy

Krishna_Murthy_1
Getting this error when tried to set the storage policy on a location [Vertica][VJDBC](5662) ERROR: Storage tier TBSPOOL1 has not been found on all nodes [SQL State=HY000, DB Errorcode=5662] and these are the steps followed as in the Vertica document(s) 1. SELECT ADD_LOCATION('/data10/xtremdb/pool_user_location', '', 'USER','TBSPOOL1'); 2. GRANT ALL ON LOCATION '/data10/xtremdb/pool_user_location' TO tuser 3. select SET_OBJECT_STORAGE_POLICY ( 'tuser', 'TBSPOOL1' ); Am I missing something here?. Thanks Krishna

    6 replies

    wwong2
    Forum|alt.badge.img
    • wwong2
    • Participating Frequently
    • July 18, 2013
    Hi Krishna - 
    The set_object_storage_policy only works with usage DATA type only. Anything written to the new object (schema, table, etc) will be written to the new storage location associated with that label.  The usage USER type is used when you need to create a storage location for non-dbadmin users so they can import from or export to the storage location.
     
    Please refer to doc link for set_object_storage_policy, https://my.vertica.com/docs/6.1.x/HTML/index.htm#19324.htm 
    As mentioned, in link, the object_name which is the first argument for set_object_name, Identifies the database object assigned to a labeled storage location. The object_name can resolve to a database, schema, or table.

    Regards
    Wayne
    Nimmi_gupta
    Forum|alt.badge.img
    • Nimmi_gupta
    • Participating Frequently
    • July 18, 2013
    Hi Krisha, You can also follow following examples. The purpose of add_location for user is so the user can get to data files, it is different than 'DATA' or 'TEMP'. here is an example from the doc. In the following series of commands, a superuser creates a new storage location and grants it to user Bob: dbadmin=> SELECT ADD_LOCATION('/home/dbadmin/UserStorage/BobStore', 'v_mcdb_node0007', 'USER'); ADD_LOCATION ------------------------------------------- /home/dbadmin/UserStorage/BobStore' added. (1 row) Now the superuser grants Bob READ/WRITE permissions on the /BobStore location: dbadmin=> GRANT ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' TO Bob; GRANT PRIVILEGE Revoke all storage location privileges from Bob: dbadmin=> REVOKE ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' FROM Bob; REVOKE PRIVILEGE Grant privileges to Bob on the BobStore location again, this time specifying the node: dbadmin=> GRANT ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' ON v_mcdb_node0007 TO Bob; GRANT PRIVILEGE Revoke all storage location privileges from Bob: dbadmin=> REVOKE ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' ON v_mcdb_node0007 FROM Bob; REVOKE PRIVILEGE Best, Nimmi
    Krishna_Murthy_1
    Hi Wayne, No where in any of the Vertica documents (Admin or SQL guides) mentioned that the set_object_storage_policy only works with usage DATA type only. Anyhow it works with DATA usage but the again grant cannot be executed for DATA usage location to a User. Is this the expected behavior?. Thanks Krishna
    Krishna_Murthy_1
    Hi Nimmi, Thanks for the example and it works by creating location with usage as USER and granting that location to a user. Thanks Krishna
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • August 10, 2013
    Hello Krishna: I've added several updates to the docs noting that you cannot create a storage policy on a USER type. Should be seen in next doc release in these places: ADD_LOCATION SET_OBJECT_STORAGE_POLICY Administator's Guide: Adding Storage Locations Creating Storage Policies Thanks for helping us clarify the docs. Kanti
    Krishna_Murthy_1
    Thanks Kanti. That would help others too in the future. Thanks Krishna
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement