Tuesday, December 11, 2007

Crontab - Quick reference

Setting up cronjobs in Unix and Solaris
cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix , solaris.
Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

Following points sum up the crontab functionality :

1. Crontab Restrictions
2. Crontab Commands
3. Crontab file - syntax
4. Crontab Example
5. Crontab Environment
6. Disable Email
7. Generate log file for crontab activity
8. Next Steps

1. Crontab Restrictions
____________
You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands
__________
export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e Edit your crontab file, or create one if it doesn't already exist.
crontab -l Display your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file. (This option is only available on a few systems.)


3. Crontab file
___________
Crontab syntax :-
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)


* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example
_______

A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.

30 18 * * * rm /home/someuser/tmp/*



Changing the parameter values as below will cause this command to run at different time schedule below :

min hour day/month month day/week Execution time
30 0 1 1,6,12 * -- 00:30 Hrs on 1st of Jan, June & Dec.
:

0 20 * 10 1-5 --8.00 PM every weekday (Mon-Fri) only in Oct.
:

0 0 1,10,15 * * -- midnight on 1st ,10th & 15th of month
:

5,10 0 10 * 1 -- At 12.05,12.10 every Monday & on 10th of every month
:

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment
___________
cron invokes the command from the user's HOME directory with the shell, (/usr/bin/sh).
cron supplies a default environment for every shell, defining:
HOME=user's-home-directory
LOGNAME=user's-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email
____________

By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1


7. Generate log file
________________
To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log

8. Next Steps
This article covered a significant aspect of system administration of setting up cronjobs . Unix administration involves lots of different tasks and some of these tasks are covered in this website but still there are many areas not covered here .

JDBC in Short

1. Types of Drivers
There are many possible implementations of JDBC drivers. These implementations are categorized as follows:
 Type 1 - drivers that implement the JDBC API as a mapping to another data access API, such as ODBC. Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an example of a Type 1 driver.
 Type 2 - drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited.
 Type 3 - drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client?s requests to the data source.
 Type 4 - drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.

Establishing a Connection
First, you need to establish a connection with the DBMS you want to use. Typically, a JDBC™ application connects to a target data source using one of two mechanisms:
• DriverManager: This fully implemented class requires an application to load a specific driver, using a hardcoded URL. As part of its initialization, the DriverManager class attempts to load the driver classes referenced in the jdbc.drivers system property. This allows you to customize the JDBC Drivers used by your applications.
• DataSource: This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source.
Establishing a connection involves two steps: Loading the driver, and making the connection.
Loading the Driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager, so you don't need to create an instance of the class. If you were to create your own instance, you would be creating an unnecessary duplicate, but it would do no harm.
Making the Connection
The getConnection method establishes a connection:
Connection conn = DriverManager.getConnection("jdbc:derby:COFFEES");
Using a DataSource Object for a connection
Using a DataSource object increases application portability by making it possible for an application to use a logical name for a data source instead of having to supply information specific to a particular driver. The following example shows how to use a DataSource to establish a connection:
You can configure a DataSource using a tool or manually. For example, Here is an example of a DataSource lookup:
InitialContext ic = new InitialContext()

DataSource ds = ic.lookup("java:comp/env/jdbc/myDB");
Connection con = ds.getConnection();
DataSource ds = (DataSource) org.apache.derby.jdbc.ClientDataSource()
ds.setPort(1527);
ds.setHost("localhost");
ds.setUser("APP")
ds.setPassword("APP");

Connection con = ds.getConnection();
DataSource implementations must provide getter and setter methods for each property they support. These properties typically are initialized when the DataSource object is deployed.
VendorDataSource vds = new VendorDataSource();
vds.setServerName("my_database_server");
String name = vds.getServerName();

Retrieving Values from Result Sets

1St Way:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(
"SELECT COF_NAME, PRICE FROM COFFEES");
while (srs.next()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}


2nd Way:


Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
srs.afterLast();
while (srs.previous()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}

Updating Tables

Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

Using Prepared Statements
If you want to execute a Statement object many times, it normally reduces execution time to use a PreparedStatement object instead.
The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.
Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections.
Creating a PreparedStatement Object
PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
Supplying Values for PreparedStatement Parameters
updateSales.setInt(1, 75);
Return Values for the executeUpdate Method

Whereas executeQuery returns a ResultSet object containing the results of the query sent to the DBMS, the return value for executeUpdate is an int that indicates how many rows of a table were updated. For instance, the following code shows the return value of executeUpdate being assigned to the variable n:
updateSales.setInt(1, 50);
updateSales.setString(2, "Espresso");
int n = updateSales.executeUpdate();
// n = 1 because one row had a change in it
The table COFFEES was updated by having the value 50 replace the value in the column SALES in the row for Espresso. That update affected one row in the table, so n is equal to 1.
When the method executeUpdate is used to execute a DDL statement, such as in creating a table, it returns the int 0. Consequently, in the following code fragment, which executes the DDL statement used to create the table COFFEES, n is assigned a value of 0:
int n = executeUpdate(createTableCoffees); // n = 0
Note that when the return value for executeUpdate is 0, it can mean one of two things:
• the statement executed was an update statement that affected zero rows

• the statement executed was a DDL statement.
Using Joins
A join is a database operation that relates two or more tables by means of values that they share in common.

Using Transactions
A transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed.
Disabling Auto-commit Mode

con.setAutoCommit(false);


Committing a Transaction
con.setAutoCommit(false);
PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 50);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
PreparedStatement updateTotal = con.prepareStatement(
"UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);

It is advisable to disable auto-commit mode only while you want to be in transaction mode. This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users.

Shell script to ftp file

ftp -n $HOST <quote USER $USER
quote PASS $PASSWD
cd $FTP_PATH
binaryput $BACKUP_PATH/$NOWDATE.tar
quit
END
EXITSTATUS=$?
if [ $EXITSTATUS != "0" ]then
echo "oops! got error, while transferring file to host m/c "
fi