Informix Experts

Matching: delete

Simple DDL and CRUD operations in four protocols

cheetahblog Tags: java delete database update jdbc collection mongodb golledge select mongo table read crud rest create user ddl ‎ 9,193 Views

Informix has four protocols, or communication channels, which you can use to access the server, SQLI, DRDA, JSON, and RESTful. If you are familiar with one of these and want to know how to perform the same basic operations using one of the others, consider this post to be your Rosetta stone.

The sample application is written in Java. It iterates through using the Informix JDBC driver (which uses the SQLI protocol), the DB2 JCC driver (which uses DRDA), the MongoDB driver for Java (JSON), and native Java HTTP classes (REST). For each of these protocols, it goes through the steps to:

Create a user
Create a database
Create a table/collection
Create a row or document (entry)
Read an entry
Update an entry
Delete entries
Drop a table/collection
Drop a database
Drop a user

The code should be self-explanatory, but there are some differences between the APIs worth noting.

Using the SQLI and DRDA protocols, connections are stateful. There is an open channel maintained between the port listening for commands on the server and the port on the client sending commands. So, transaction states are easily maintained, and multiple commands can be executed without re-authenticating. Most operations are identical between these two, except the URLs are different. Informix JDBC requires an INFORMIXSERVER setting. You can not drop the database you are currently accessing. Authentication takes place at the server level, allowing you to access whatever database for which you have appropriate privileges.

The MongoDB clients are what I'll call semi-stateful. Client and db objects have the appearance of maintaining a connection with the database server, but really the connection information is cached on the client side and as far as the listener is concerned every operation is independent. (Exceptions to this model are beyond the scope of this write-up.) The database you are dropping must be the current database. Authentication takes place at the database level.

Fitting the REST model, there is no concept of a stateful connection in the RESTful API. However, there are two ways to authenticate: Providing a user name and password as is common, and sending a session id cookie from a previously authenticated operation. Using a session cookie is how you can, say, authenticate against the admin database, and use your userAdminAnyDatabase privilege to create a new user within another database.

Users with the ability to create other users must already exist for this demo to work. Examples:

Informix DBSA: create user dbsauser with password 'a1b2c3d4e5' properties user 'nobody' authorization(DBSA)

MongoDB

2.4: use admin

db.addUser( { user: "userAdmin", pwd: "1RDSRDS", roles: [ "userAdminAnyDatabase" ] } )

2.6: db.createUser({user: "userAdmin", pwd: "1RDSRDS", roles: [ { role: "userAdminAnyDatabase", db: "admin" } ] })

The download file contains the source code and Eclipse project that can be used to build it. It also contains all the required drivers; it is your choice whether to use the ones included or use more modern ones you have installed. Included in the project is an ANT script for building the jar.

Run the demo with the command: java -jar basicChecks.jar connSettings.properties

Running the demo without the required properties file will give you instructions for what the file should contain.

$ java -jar basicChecks.jar
Usage: java Main <connection properties file>
The connection properties file should contain lines of <name>=<value> pairs.
Example:
host=myHost.domain.com
sqli_port=9088
informixserver=myServerName
drda_port=9089
mongodb_port=27017
rest_port=27018
user_admin=jane
user_admin_pwd=54cba
dbsa_user=bob
dbsa_user_pwd=abc45

-Chris Golledge

Download the demo.

Modified on by cheetahblog

Managing Log Files

cheetahblog Tags: delete rotate truncate file ‎ 13,463 Views

How often you purge Informix log files? I am sure most of you cleanup your log files on regular basis. However there are people, to whom purging log files comes as a reactionary in troubleshooting a file system full situation. Do you know there is way within Informix to programmatically manage common log files e.g., message.log, bar_act.log, bar_debug.log etc. Informix introduces this capability in version 11.70.xC3 to target embedded environment, where disk space is fixed and database instance must run years without intervention from administrators. Nevertheless administrators can take the advantage of this functionality to organize Informix log files outside the embedded environment also. The Auto Rotate Log feature provides a platform independent way to automatically manage Informix log files. This feature implemented via database scheduler that provides the flexibility to use it from dbaccress using SQL admin API, as well as from OAT.

Using this feature you can rotate, truncate, or delete log files. It can be run manually or automatically. However, to perform this operation automatically you need to enable it first. There are several new admin API commands added for this feature:

file rotate
file truncate
file delete

Let’s take a look couple of examples for better understanding.

Manually Rotate Log File
The rotate operation switches to a new log file and increments the ID numbers for the previous log files by one. When the maximum number of log files is reached, the oldest log file deleted. To rotate the "/usr/informix/online.log” and keep last 10 files use:
   EXECUTE FUNCTION task("file rotate", "/usr/informix/online.log", 10);

Manually Truncate Log File
The truncate operation removes the contents but keep the log file. To truncate "/usr/informix/bar_act.log", specify:
   EXECUTE FUNCTION task("file truncate", "/usr/informix/bar_act.log");

Manually Delete Log File
The delete operation removes the file. To delete the entire "/usr/informix/bar_debug.log" file, specify:
   EXECUTE FUNCTION task("file delete", "/usr/informix/bar_debug.log");

Automatic Rotate Log
There are three tasks added to sysadmin:ph_task task table to automatically rotate log files, for example: online_log_rotate, bar_act_log_rotate and bar_debug_log_rotate. The tk_enable column in sysadmin:ph_task table for appropriate task needs to be enabled to rotate log automatically. You only need to enable each task once.

You can manage log files from OAT v.2.73 and higher. The rotate, delete and truncate log operations are available under “Logs” section on the left pane.

This is a neat and useful feature that can make administrator’s life little easier.

-Sanjit Chakraborty

Feed for Blog Entries

Blogs