Friday, January 15, 2010

Commerce Developer : Working with ORACLE

As a commerce developer working with Oracle back end, it is very important to have good understanding of SQL.

I am not a DBA, but the way I have learned was to started with basic Insert/Update/Delete, Select commands and using group by, order by
but progressively learned some handy SQL commands.

Refresh local schema for development.
I would use the script below for creating tablespace/schema/assign grants and import schema. Replace wcsatdev with the schema name of your choice.

drop tablespace atdata including contents and datafiles;
drop tablespace atindx including contents and datafiles;

create tablespace atdata
datafile 'C:\oracle\product\ORCL10G\atdata_01.dbf' size 250m reuse autoextend on;

create tablespace atindx
datafile 'C:\oracle\product\ORCL10G\atindx_01.dbf' size 250m reuse autoextend on;

drop user wcsatdev cascade;

create user wcsatdev identified by wcsatdev
default tablespace atdata
temporary tablespace temp
quota unlimited on atdata
quota unlimited on atindx;

grant connect,resource,create materialized view to wcsatdev;

grant create view to wcsatdev;

grant create synonym to wcsatdev;

create or replace directory dpdumpdir as 'C:\projects\db';

create or replace directory dplogdir as 'C:\projects\db';



Using data pump in Oracle 10g, faster way to import.
impdp system/oracle@orcl10g dumpfile=DPDUMPDIR:export.dmp logfile=DPLOGDIR:from_wcsatdev_10g.log REMAP_SCHEMA=from_schema:to_schema PARALLEL=8 CONTENT=ALL

When ever you have a larger database on your local database, it is a good idea to run DB stats once in a while to improve performance.

exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'WCSATDEV',estimate_percent=>dbms_stats.auto_sample_size, CASCADE=>TRUE, DEGREE=>4)

Where degree will invoke the 4 parallel slaves, cascade is required for indexes.

Size of the Tables: Run this command in the schema

select SEGMENT_NAME,sum(BYTES)/(1024) size_in_kil from user_extents where segment_type='TABLE' group by SEGMENT_NAME order by size_in_kil desc;

Dropping Stage prop Triggers: Stage prop is a completely new discussion but on my local I run this for better performance.

set pages 0 lines 100 ;
spool drop_trigger1.sql ;
select 'drop trigger '||trigger_name||' ;' from user_triggers where trigger_name like '%STG_%' or trigger_name = 'STGLOG_STGRESERVED1' ;
spool off;

Issues and Fixes:

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

check for this. select count(*) from v$open_cursor;

Login with system previleges and type show parameter OPEN_CURSOR

Alter cursors

Soln: restart DB

Error: ORA-12514: TNS: listener does not currently know of service requested in connect descriptor.

I made these changes and since then i did not see the problem.
ORACLE_HOME\network\admin\listener.ora, Please look for HOST and change that to
HOST=Windows hostname
For the 10g local instance tnsentry: give localhost instead of the windows hostname

Thursday, January 14, 2010

Debug: Generic System Error Test JSP

This would happen due to the descripency between supported languages in wc-server.xml and language table.


Wednesday, January 6, 2010

EJB Files to be Checked Into CVS

Creating an EJB bean and best practices to checkin files into CVS for 6.0.

The most important rule, while creating EJB's is to make sure, you understand the data model and finding the composite key that would identify the table.
e.g. extending catentry_ext (catentry_id,store_id), There should be just 1 ejbcreate with both the values.
e.g. extending catgroupdesc_ext (catgroup_id, language_id), ejbcreate(catgroupId,langId)
Also making sure the finders exist with the composite key.
e.g. findByCatentryIdStoreId and findByCatGroupIdAndLangId

I have used the following link to create multiple EJB's successfully.

2. I would usually be careful at step 15. to map the entity bean to the table, some times has issues when you try to map in wrong view. Other than that all other steps are required.

3. Best practice to check in files as a practice. Usually the following files are checked in and an ant task is used during build to generate all the stub classes for EJB's and access beans. It is better to not checkin all the generated classes to CVS. This approach would make it cleaner.



4.Do not check-in access beans, use a build script to create access beans, when refreshing code from version control system such as CVS or SVN.
To test it, you can use access bean to create a new row and commit.

Interesting things. Why do you use access beans?
Access beans are wrappers around EJB's that providers getters and setters to the fields and are also used to improve the performance of an data update operations, where in multiple columns are written to first Hashmap internally and committed to the database.