DB6CONV 6.30 -Conversions from Row to Column or Column to Row Organisation
DB6CONV6 – Program to Convert BW objects from Row to Column or Columnar to Row organisation.
AUTHOR : ROHIT KALAGE
Audiance – This blog post is specifically for the SAP BW consultants,administrators who work on SAP BW7.4 systems on IBM DB2 for Linux,Unix and Windows.
This program DB6CONV is basically used to convert single table or multiple tables means move tables from one table space to another.
This particular blog is moreover related to BW perspective where DB6CONV would be used to convert row organised table to column organised table or vice versa.
In Detail –
1) DB6CONV is not standard library content.This has to installed through SAP note 1513862.To make this program useful for BW, database version should have to be DB2 10.5 FP5SAP2 or higher and basis version should be 7.00 or higher.
How to Perform BW Conversions using DB6CONV?
1) After installing and transporting SAP note 1513862,program DB6CONV will be available through SE38.
2) We will see following screen after executing the program in SE38.
3) Lets discuss in detail how to do the BW Conversions.
a) Click on New conversion button.You will be seeing following screen.
Tab Business Warehouse is the only tab which is in the scope of this paper.
b) As you are aware,everything is in the form of tables in BW,so first of all decide what to convert.
Provision is there for Infoobject,PSA,Infocube,DSO.So lets for example we want to covert a Cube.Enter the cube name.
c) Then check Infocube checkbox,uncheck all others in BW Object Type.
d) In Options tab check ‘Include Dependent Info-object only if you want to convert all tables related to every info object present in the cube.
i)Target:Blu Acceleration :- This radio button is to convert Row organised object to column organised object.
Offline mode- Not available for INSERT,UPDATE,DELETE while Converting.
Online mode- Available for INSERT,UPDATE,DELETE while Converting.
Converting from Row organised to Column organised can be done
in both the modes.
ii) Target:Row-Organised :- This radio button is to convert Column organised to row organised object.
Converting from Row organised to Column organised can be done
only and only offline mode.
iii)Keep Table Organisation– Not in scope of BW conversion.
iv) BW Post-Processing only – Discuss it later.
f) Create queue :- After clicking on this a queue entry will be created with no. of objects,
no. of tables and size of all the tables in the queue.
You can edit/display queue as well.
g) target table space:- This comes with Keep Table organisation to move tables from one table space to other.Right now it is not in scope of this.
h)Next Part with settings-
i) COPY with Indexes – This checkbox copies indexes first and then data transfer.
This comes preselected but can be unchecked to save time to convert but will increase temporary space.
ii) USE LOAD(Recoverable/Non-Recoverable) – LOAD command is improvised INSERT command,Conversions will be much more faster with this option.
This option only applicable if table size is more than 2MB.
Recoverable – Want to go through the trace/log of conversion. Need to mention path of file for conversion option COPY YES TO and for the profile LOAD.
Non Recoverable – The logs cannot be maintained.
iii) REDIRECT and STOP BEFORE SWITCH not relevant to conversions.
iv)Delete Source table after conversion –
Mention if you want the source table to be dropped after conversion.
If any error while converting,this option can be used.Trace file will be written to db2dump directory.
vi) Compression for Data/Indexes – This will always be KEEP state.This cant be changed,This is by default.
After this click on save,
This will give you following messages-
click OK then
now again click OK will take you to main screen where a new job will be created which will be in planned state-
Lets see how to read and monitor jobs now.
Now the job will be in ‘PLANNED’ state.Once we click on Start,popup will ask for scheduling option and no. of parallel processes.After clicking OK,Job will go in running state.
After running state it wll go to ‘FINISHED’ if finishes successfully if not will go in ‘ABORTED’ state.
We can even PAUSE the conversion and again REPLAY or RESET it.
This conversion process will follow following process.
And thus conversion will takes place.
How to Read the Job Details or Logs?
Basically there are two windows for conversions- Conversion Jobs and Tables from Mass Conversions.
In Conversion Jobs- we see the single job created for entire conversion object whereas next window will be for conversions of every single table of object from given job with addition of BW Post processing step.
For example if we are converting 0MATERIAL then in first window we will One row with with status ‘PLANNED’.Then object type as BWCONV by default.Object name as subsequent job name from previous job starting from <BW>_000001.
Then start date,start time,End Date,End time.After that duration for which conversion is running.The Size that finally converted.Then Jobs will give no.of processes allocated for the job and then comments as.
[a]/[b] tables (x k bytes) - [m]/[n] objects
[a]- No of tables converted from an object. [b]- Total no. of tables from an object x - Size of table converted. [m] - No. of objects converted from queue. [n] - Total no. of objects to be converted from queue.
Similar is the case with next window which will give details table wise which are present in particular job.
We can look of every single conversion job after clicking JOB row and then on Conversion Log.
What is BW post processing step?What to do if that step fails?
This BW post processing step is specifically to update metadata after conversion.If this step fails then we have to create again a new queued job with radio button ticked as ‘BW Post Processing Only’ and then execute. Thats it.
Can we take more than one objects in the queue?
Yes.We can.We can use * for all objects or for for specific type like PSA. Even after creating queue,we can edit entries in the queue.
How to set parameter for LOAD command with Recoverable option?
Click on Conversion option and click on Add.
Then Keep Profile : ‘LOAD’.
Option – COPY YES
Value as To /(file name in directory).
If ths parameter setting goes wrong,conversion job may fail.
How to monitor job and how tobe sure of conversion?
We can monitor job in SM37 as well.And to check table organisation before and after conversion we can check this in DB02 transaction.
We could see 50-60% size reduction after converting a table to Column organised.
And read performance improvement for master data objects after converting those to Row organised.
1)This Program version is DB6CONV 6.30.which is latest as of now.
2) We can delete job detais if not needed.
3) If row converted object again added in the queue to convert it to row organised then program will throw a message of ‘NOT A RIGHT CANDIDATE FOR CONVERSION.