Customizing IBM Cloud SQL Query Behavior
Follow the steps below to configure the cmdlet defaults for querying IBM Cloud SQL Query.
Checking the Job Status
Once a job is initiated server-side, IBM Cloud SQL Query automatically checks for this job to finish processing. You can use PollingInterval to configure the wait time of the requests that the provider is doing towards the job status.
Note that some queries may be complex and it will take longer for the result to be retrieved, that is why the PollingInterval might be configured to be longer in order not to make so many unnecessary requests. A job is initiated server-side only when the SELECT query is not a simple "Select * FROM Table" query but it has at least a criteria or projection in it. The outcome of each job is considered a view as it supports only "Select" operation.
Cleaning Query Results
IBM Cloud SQL Query stores the results of every query you execute in CSV or JSON files in ResultLocationURI. You can use CleanQueryResults, to automatically clean these files for every query executed.
Note that this behavior will add a minor performance hit when you disconnect the last connection in a process.
Using IBM Cloud's Query Caching
You configure QueryCachingLevel to modify the usage of the query results stored in ResultLocationURI. This is especially helpful when executing a certain query multiple times. This means IBM Cloud SQL Query will not scan the same data again and simply use the results from the previous execution. These results are not used if the job was started earlier than the amount of seconds specified in QueryTolerance.
A default bucket may be specified by setting the Bucket and connection property. This is optional and will be used if you do not specify a bucket when issuing a query. If no Bucket is set, then the bucket name must be used as the schema in the query in the format [Schema].[Table].
Results may be stored in different data types. By default, the CData Cmdlets PowerShell Module for IBM Cloud SQL Query will use CSV. This may be altered by modifying StoreResultsAs.
Customizing the SSL Configuration
By default, the cmdlet attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.
Connecting Through a Firewall or Proxy
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.
In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Set the following properties:
- To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
- To tunnel the connection, set FirewallType to TUNNEL.
- To authenticate, specify FirewallUser and FirewallPassword.
- To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.
Troubleshooting the Connection
To show cmdlet activity from query execution to network traffic, use Logfile and Verbosity. The examples of common connection errors below show how to use these properties to get more context. Contact the support team for help tracing the source of an error or circumventing a performance issue.
- Authentication errors: Typically, recording a Logfile at Verbosity 4 is necessary to get full details on an authentication error.
- Queries time out: A server that takes too long to respond will exceed the cmdlet's client-side timeout. Often, setting the Timeout property to a higher value will avoid a connection error. Another option is to disable the timeout by setting the property to 0. Setting Verbosity to 2 will show where the time is being spent.
- The certificate presented by the server cannot be validated: This error indicates that the cmdlet cannot validate the server's certificate through the chain of trust. If you are using a self-signed certificate, there is only one certificate in the chain.
To resolve this error, you must verify yourself that the certificate can be trusted and specify to the cmdlet that you trust the certificate. One way you can specify that you trust a certificate is to add the certificate to the trusted system store; another is to set SSLServerCert.