HCL OneDB JSON commands

The HCL OneDB™ JSON commands are available in addition to the supported MongoDB commands. These commands enable functionality that is supported by HCL OneDB and they are run by using the MongoDB API.

The syntax for using HCL OneDB commands in the MongoDB shell is: 
db.runCommand({command_document})
The command_document contains the HCL OneDB command and any parameters.

createTextIndex

Create HCL OneDB Basic Text Search (BTS) indexes.

Important: If you create text indexes by using the HCL OneDB createTextIndex command, you must query them by using the HCL OneDB $ifxtext query operator. If you create text indexes by using the MongoDB syntax for text indexes, you must query them by using the MongoDB $text query operator.
createTextIndex : " collection_name " , name : " indexName " [ , key : { " column " } ] , options : { [ <btx index parameters> (explicit id ) ] }
createTextIndex
This required parameter specifies the name of the collection or relational table where the BTS index is created.
name
This required parameter specifies the name of the BTS index.
options
This required parameter specifies the name-value pairs for the BTS parameters that are used when creating the index. If no parameter values are required, you can specify an empty document.
Use BTS index parameters to customize the behavior of the index and how text is indexed. Include JSON index parameters to control how JSON and BSON documents are indexed. For example, you can index the documents as field name-value pairs instead of as unstructured text so that you can search for text by field. The name and values of the BTS index parameters in the options parameter are the same as the syntax for creating a BTS access method with the SQL CREATE INDEX statement. The following example creates an index named articlesIdx on the articles collection by using the BTS parameter all_json_names="yes":
db.runCommand({ 
 createTextIndex:"articles", 
 name:"articlesIdx", 
 options:{all_json_names:"yes"}})
key
This parameter is required if you are indexing relational tables, but optional if you are indexing collections. This parameter specifies which columns to index for relational tables.
The following example creates an index named myidx in the mytab relational table on the title and abstract columns:
db.runCommand({ 
 createTextIndex:"mytab", 
 name:"myidx", 
 key:{"title":"text","abstract":"text"}, 
 options:{}})

exportCollection

Export JSON collections from the wire listener to a file.

exportCollection : " collection_name " , file : " filepath " , format : { { " json " | " jsonArray " } [ , fields : { " filter " } ] | " csv " , fields : { " filter " } } [ , query : { " query_document " } ]
exportCollection
This required parameter specifies the collection name to export.
file
This required parameter specifies the output file path on the host machine where the wire listener is running. For example:
  • UNIX™ is file:"/tmp/export.out"
  • Windows™ is file:"C:/temp/export.out"
format
This required parameter specifies the exported file format.
json
Default. The .json file format. One JSON-serialized document per line is exported.
The following command exports all documents from the collection that is named c by using the json format: 
> db.runCommand({exportCollection:"c",file:"/tmp/export.out"
 ,format:"json"})
The result of this command will look like this: 
{
  "ok":1,
  "n":1000,
  "millis":NumberLong(119),
  "rate":8403.361344537816
}
Where "n" is the number of documents that are exported, "millis" is the number of milliseconds it took to export, and "rate" is the number of documents per second that are exported.
jsonArray
The .jsonArray file format. This format exports an array of JSON-serialized documents with no line breaks. The array format is JSON-standard.
The following command exports all documents from the collection c by using the jsonArray format: 
> db.runCommand({exportCollection:"c",file:"/tmp/export.out"
 , format:"jsonArray"})
{
	"ok":1,
	"n":1000,
	"millis":NumberLong(81),
	"rate":12345.67901234568
}
Where "n" is the number of documents that are exported, "millis" is the number of milliseconds it took to export, and "rate" is the number of documents per second that are exported.
csv
The .csv file format. Comma-separated values are exported. You must specify which fields to export from each document. The first line of the .csv file contains the fields and all subsequent lines contain the comma-separated document values.
fields
This parameter specifies which fields are included in the output file. This parameter is required for the csv format, but optional for the json and jsonArray formats.
The following command exports all documents from the collection that is named c by using the csv format, only output the "_id" and "name" fields: 
> db.runCommand({exportCollection: "c" ,file:"/tmp/export.out",
 format: "csv", fields: {"_id": 1, "name": 1}})
query
This optional parameter specifies a query document that identifies which documents are exported. The following example exports all documents from the collection that is named c that have a "qty" field that is less than 100: 
> db.runCommand({exportCollection: "c", file: "/tmp/export.out",
  format: "json", query: {"qty": {"$lt": 100}}})

importCollection

Import JSON collections from the wire listener to a file.

importCollection : " collection_name " , file : " filepath " , format : " { json | jsonArray | csv } "
importCollection
The required parameter specifies the collection name to import.
file
This required parameter specifies the input file path. For example, file: "/tmp/import.json".
Important: The input file must be on the same host machine where the wire listener is running.
format
This required parameter specifies the imported file format.
json
Default. The .json file format.
The following example imports documents from the collection that is named c by using the json format: 
> db.runCommand({importCollection: "c", file: "/tmp/import.out",
  format:"json"})
jsonArray
The .jsonArray file format.
The following example imports documents from the collection c by using the jsonArray format: 
> db.runCommand({exportCollection: "c", file: "/tmp/import.out",
  format:"jsonArray"})
csv
The .csv file format.

lockAccounts

Lock a database or user account.
Important:
  • To run this command, you must be the instance administrator.
  • If you specify the lockAccounts:1 command without specifying a db or user argument, all accounts in all databases are locked.
lockAccounts : { 1 [ { , db : { " database_name " | [ " database_name " ] | { " $regex " : " json_document " } | { { | " include " : { " database_name " | [ " database_name " ] | { " $regex " : " json_document " } } | " exclude " : { " database_name " | [ " database_name " ] | { " $regex " : " json_document " } } } } } " | , user : { " user_name " | " json_document " } } ] }
lockAccounts:1
This required parameter locks a database or user account.
db
This optional parameter specifies the database name of an account to lock. For example, to lock all accounts in database that is named foo:
> db.runCommand({lockAccounts: 1 ,db: "foo"})
exclude
This optional parameter specifies the databases to exclude. For example, to lock all accounts on the system except the accounts that are in the databases named alpha and beta:
> db.runCommand({lockAccounts: 1, db: {"exclude": ["alpha", "beta"]})
include
This optional parameter specifies the databases to include. For example, to lock all accounts in the databases named delta and gamma:
> db.runCommand({lockAccounts: 1, db: {"include": ["delta", "gamma"]})
$regex
This optional MongoDB evaluation query operator selects values from a specified JSON document. For example, to lock accounts for databases that begin with the character a. and end in e:
> db.runCommand({lockAccounts: 1, db: {"$regex": "a.*e"})
user
This optional parameter specifies the user accounts to lock. For example, to lock the account of all users that are not named alice:
> db.runCommand({lockAccounts: 1, user: {$ne: "alice"}});

runFunction

Run an SQL function through the wire listener. This command is equivalent to the SQL statement EXECUTE FUNCTION.

runFunction : " function_name " [ , " arguments " : [ argument ] ]
runFunction
This required parameter specifies the name of the SQL function to run. For example, a current function returns the current date and time:
> db.runCommand({runFunction: "current"})
{"returnValue": 2016-04-05 12:09:00, "ok":1}
arguments
This parameter specifies an array of argument values to the function. You must provide as many arguments as the function requires. For example, an add_values function requires two arguments to add together:
> db.runCommand({runFunction: "add_values", "arguments": [3,6]})
{"returnValue": 9, "ok": 1}
The following example returns multiple values from a func_return3 function:
> db.runCommand({runFunction: "func_return3", "arguments" :[101]})
{"returnValue": {"serial_num": 1103, "name": "Newton", "points": 100}, "ok": 1}

runProcedure

Run an SQL stored procedure through the wire listener. This command is equivalent to the SQL statement EXECUTE PROCEDURE.

runProcedure : " procedure_name " [ , " arguments " : [ argument ] ]
runProcedure
This required parameter specifies the name of the SQL procedure to run. For example, a colors_list stored procedure, which uses a WITH RESUME clause in its RETURN statement, returns multiple rows about colors:
> db.runCommand({runProcedure: "colors_list"})
{"returnValue": [
  {"color" : "Red","hex" : "FF0000"},               
  {"color" : "Blue","hex" : "0000A0"},               
  {"color" :"White","hex" : "FFFFFF"}
], "ok" : 1}
arguments
This parameter specifies an array of argument values to the procedure. You must provide as many arguments as the procedure requires. For example, an increase_price procedure requires two arguments to identify the original price and the amount of increase:
> db.runCommand({runProcedure: "increase_price", "arguments": [101, 10]})
{"ok":1}

transaction

Enable or disable transaction support for a session, run a batch transaction, or, when transaction support is enabled, commit or rollback transactions. This command binds or unbinds a connection to the current MongoDB session in a database. The relationship between a MongoDB session and the HCL OneDB JDBC connection is not static.
Important: This command is not supported for queries that are run on shard servers.
transaction : { " enable " | " disable " | " commit " | " rollback " | " execute " , " commands " : [ command_docs ] [ , " finally " : [ command_docs ] ] | " status " }
enable
This optional parameter enables transaction mode for the current session in the current database. The following example shows how to enable transaction mode:
> db.runCommand({transaction: "enable"})
{"ok":1}
disable
This optional parameter disables transaction mode for the current session in the current database. The following example shows how to disable for transaction mode: 
> db.c.find()
{"_id":ObjectId("52a8f9c477a0364542887ed4"),"a":1}
> db.runCommand({transaction: "disable"})
{"ok":1}
commit
If transactions are enabled, this optional parameter commits the current transaction. If transactions are disabled, an error is shown. The following example shows how to commit the current transaction:
> db.c.insert({"a": 1})
> db.runCommand({transaction: "commit"})
{"ok":1}
rollback
If transactions are enabled, this optional parameter rolls back the current transaction. If transactions are disabled, an error is shown. The following example shows how to roll back the current transaction:
> db.c.insert({"a": 2})
> db.c.find()
{"_id":ObjectId("52a8f9c477a0364542887ed4"),"a":1}
{"_id":ObjectId("52a8f9e877a0364542887ed5"),"a":2}
> db.runCommand({transaction: "rollback"})
{"ok":1}
execute
This optional parameter runs a batch of commands as a single transaction. If transaction mode is not enabled for the session, this parameter enables transaction mode for the duration of the transaction.
The list of command documents can include insert, update, delete, findAndModify, and find command documents. In insert, update, and delete command documents, you cannot set the ordered property to false. You can use a find command document to run queries, including SQL queries, but not commands. A find command document can include the $orderby, limit, skip, and sort operators. The following example deletes a document from the inventory collection and inserts documents into the archive collection:
> db.runCommand({"transaction" : "execute", 
 "commands" : [
	{"delete": "inventory", "deletes" : [ { "q" : { "_id" : 432432 } } ] },
	{"insert" : "archive", 
	 "documents" : [ { "_id": 432432, "name" : "apollo", "last_status" : 9} ]
        }
 ]
})
Include the optional finally argument if you have a set of command documents to run at the end of the transaction regardless of whether the transaction is successful. The following example runs a query with the . The command document for the finally argument unsets the USE_DWA environment variable regardless of whether the previous query succeeds. 
> db.runCommand({"transaction" : "execute", 
 "commands" : [
    {"find" : "system.sql", "filter" : {"$sql" : 
              "SET ENVIRONMENT USE_DWA 'ACCELERATE ON'" } },
    {"find" : "system.sql", "filter" : {"$sql" : 
              "SELECT SUM(s.amount) as sum FROM sales AS s 
              WHERE s.prid = 100 GROUP BY s.zip" } }
 ], 
"finally" : [{"find":"system.sql", "filter" : {"$sql" : 
             "SET ENVIRONMENT USE_DWA 'ACCELERATE OFF'" } } ]
})
status
This optional parameter prints status information to indicate whether transaction mode is enabled, and if transactions are supported by the current database. The following example shows how to print status information:
> db.runCommand({transaction: "status"})
{"enabled": true, "supports": true, "ok": 1}

unlockAccounts

Unlock a database or user account.
Important:
  • To run this command, you must be the instance administrator.
  • If you specify the unlockAccounts:1 command without specifying a db or user argument, all accounts in all databases are unlocked.
unlockAccounts : { { 1 [ { , db : { " database_name " | [ " database_name " ] | { " $regex " : " json_document " } | { { | " include " : { " database_name " | [ " database_name " ] | { " $regex " : " json_document " } } | " exclude " : { " database_name " | [ " database_name " ] | { " $regex " : " json_document " } } } } } " | , user : { " user_name " | " json_document " } } ] } }
unlockAccounts:1
This required parameter unlocks a database or user account.
db
This optional parameter specifies the database name of an account to unlock. For example, to unlock all accounts in database that is named foo:
> db.runCommand({unlockAccounts: 1, db: "foo"})
exclude
This optional parameter specifies the databases to exclude. For example, to unlock all accounts on the system except the accounts that are in the databases named alpha and beta:
> db.runCommand({unlockAccounts: 1, db: {"exclude": ["alpha", "beta"]})
include
This optional parameter specifies the databases to include. For example, to unlock all accounts in the databases named delta and gamma:
> db.runCommand({unlockAccounts: 1, db: {"include": ["delta", "gamma"]})
$regex
This optional MongoDB evaluation query operator selects values from a specified JSON document. For example, to unlock accounts for databases that begin with the character a. and end in e:
> db.runCommand({unlockAccounts:1, db:{"$regex":"a.*e"})
user
This optional parameter specifies the user accounts to unlock. For example, to unlock the account of all users that are not named alice:
> db.runCommand({unlockAccounts: 1, user: {$ne: "alice"}});
1 See bts access method syntax.