Category Archives: Technical

29 Jun

Restore A Table / Database From Full Backup – Yet Another Way

Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available.
In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario.

The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table.

As Linux players we already know of some tools and techniques to export a table or database from mysqldump – for example, using sed command or using the script mysqldumpsplitter (based on sed itself). But on Windows we are powerless by not being able to use sed (we’re sad without sed.) Also, there was no cygwin to ease up the pain.

We had to come-up with a solution that works on Windows as well. During this discussion, my Pythian colleague, Romuller, suggested a cool but simple trick which enlightens us and offers one more way of exporting or recovering a table from a full mysqldump.

So the trick here is as follows:

Read More

29 Jan

Export MySQL database table to CSV (delimited / Excel) file

Today lets talk a little about converting a MySQL table to CSV (Excel). My friend was looking to export MySQL to Excel, I saw couple of questions for export MySQL tables to CSV on forums. Since I saw the question often, I thought of writing out all the ways I can think of for exporting Delimited (CSV / TSV / …) data from MySQL table. Pretty chewed & basic but frequent topic.

Following are the ways to export CSV data from MySQL database / table(s).

1. Using SELECT INTO … OUTFILE statement to export from MySQL to CSV

SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format. Just to mention, SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE, which you may use to load CSV (generally speaking delimited) files to MySQL.

Here’s a sample command to export “tablename” table of “db” database as a CSV:

SELECT * INTO OUTFILE '/csv_files/db.tablename.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM db.tablename;

Read More

09 Jan

Linux shell script: Pause (Suspend) a process with kill signals

Linux KILL command gives you killing opportunities to use SIGNALs. In this post we’re going to Pause (or suspend) and Start an ongoing Linux process.

Problem: The requirement here is to pause a process for certain time and run it only during off hours. The process itself doesn’t have this option and also we cannot stop it in-between, as it will start from the scratch again.

Solution: To resolve this we can think of sending out signals to the process to hint it to Pause or Progress.

A signal is an asynchronous notification sent to a process or to a specific thread within the same process in order to notify it of an event that occurred. When a signal is sent, the operating system interrupts the target process’ normal flow of execution to deliver the signal.

Read More

14 Dec

Decoding Siebel Audit Trail with PLSQL

In this post we will understand how to decode Seibel Audit Trail using PLSQL.

Siebel Audit Trail is feature in Siebel CRM which allows customer to track changes to important UI fields. Though it is very useful feature, the changes are stored in encoded text in DB. We are aiming to decode this complex data and allow user to extract complete Audit Trail information in files or tables. This can be further used to enhance customer experience.

This extract process is written completely in PL-SQL procedure. Similarly a script based solution would follow. This PL SQL procedure is tested with version 8.0 & 8.1. It should also be applicable for newer 8.1.1.16 versions as well.
So let’s get started with this. We will go step by step and understand the purpose of this code while going along with a scenario.

WHAT is Audit Trail?

Read More

07 Jun

XML to JSON converter in Siebel

Often we come across requirement for Siebel to integrate with external applications. Siebel provides various ways to integrate through EAI (Enterprise Application Integration) like EIM, Prebuilt connectors, various queues like MSMQ, JMS, WebSphere MQ. Also Web service based integration using SOAP. Siebel also supports RESTful based integration using Fusion Middleware.

One important feature of Siebel EAI is EAI Java Business Service which can be used to invoke external Java class to perform various operations.
In this article we will see how we can use various EAI Business services to communicate with JSON API of external application. We will have an XML input processed and resulted into a JSON output in Siebel.

Read More

-- Kedar Vaijanapurkar --