Data Cleansing

So much database types as well as system weared. Where the system have to support execution of work but in other matter will very complicate in the case of reporting system, because possibly one of the problem of datas amount which so much, disagree with used systems. It is of course become dilemma in making of reliable report and data integration which earn to be trusted.

Challenging from qualified management data is a[n critical component concerning business guarantee. In this time many technologies supporting from various aspect of " extract, transform, load and ( ETL) process, is but designed especially for the examination of, authentication, and sweeping for the things assuring qualified data. Technology like that have to can access and refine / tapping data of any source, including former data and data system of non-relational. That have to provide a[n step make an audit of when testing data to ascertain the integrity of and identify all and anomalies of inconsistencies. That Thing have to convert, cleaning, readjusting, and strengthening data is such as needed. Also have to can unite and clean ( data cleansing) in final repository, providing analysis and give report detailedly and summary. Finally, can walk very efficient, timely, costeffective in processing data.

Trust of data which do not consistence, complete ketidak or mistake of data can become " Risk Business" unacceptable. Data quality is a[n vital component as guarantee of[is continuity of company business. Quality of bad data can endanger attainment of efficiency and target of operational system. That Thing also can erod value of business information system, above of him entrust to make decision. based on decision is bad data, can cause monetary loss directly, destroy network, and destroy company credibility. Some companies recognize data as strategic asset, leader of business hold responsibility to ascertain correctness, quality of, and reliability of information.

In a Data Base figuring in more than 20 million data archives, prima facie strength [is] efesiensi shortened process time, delivery of data ( Extract), Transfer of into other form ( Transform) and Inclusion / Import ( Load) - ( ETL Process). The same as sweeping of effective data, conversion, and authentication is important for the success of than equipment of Data Base.

Data of Cleansing is covering some processes as follow:
1. Data Definition
Intake process ( Extract) Data of Database and or from other system, from various format type and data base type like Oracle, AS400, Dbase, Foxpro, SQL, Excel, Text, and others). Data Extract can be conducted by 2 methods:
• Direct Method
Data taken by connect is direct the than Database, with snapshot technique - only read henceforth to process.
• Indirect Method
Data taken indirectly from Database conducted process degradation of Data beforehand to format . txt henceforth to process.

2. Verification and Data Integration
Process Verification Data is process inspection of data per record pursuant to the data type and field of like format of date, ascii, numeric. etc
Target of process the data verification is to ascertain entire/all data to be processed have as according to format of Source got Data of Text / Database..

3. Data Cleansing
Process Data of Cleansing, in this case that is doing standardization in entire/all database to be content than the data base as a whole according to the format form or standard of [is] same, like determination :
• Writing of Block letters / Lower Case
• Writing of title
• Uniforming of Abbreviation word
• Tanda baca (Ex : - _ . , ; and etc)
• Conversion Letter of Romawi (ex: V = 5)
• etc

4. Data Identification
Process identify can be done by using some conditions pursuant to fringe which have been determined. As for obtained result from identifying data earn like.:
• Active Customer number
• Customer Non Active (based on criteria)
• Data Duplication
• Single Data

5. Data Matching
Adjustment process or adaptation of data ( " Match" or " Note of Match") pursuant to some alternatives like :Nama Customer dan Alamat Customer
• Customer Number
• etc
read more......

Getting Most Out of Oracle 8i/9i Statspack

Statspack is a set of performance monitoring and reporting utilities provided by Oracle for Oracle8i and above. A user is automatically created by the installation script - this user, PERFSTAT, owns all objects needed by this package. For more information about Statspack, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.

Install Statspack
To install the package, either change to the ORACLE_HOME rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin directory when calling the installation script, SPCREATE.

To run the installation script, you must use SQL*Plus and connect as a user with SYSDBA privilege. For example, start SQL*Plus, then:

On UNIX : SunOS/HP UX/Linux

SQL> CONNECT / AS SYSDBA
SQL> @?/rdbms/admin/spcreate

On Windows: XP/NT/2000/2003

SQL> CONNECT / AS SYSDBA
SQL> @%ORACLE_HOME%rdbmsadminspcreate

The SPCREATE install script runs three other scripts. These scripts are called automatically, so you do not need to run them:

* SPCUSR: Creates the user and grants privileges
* SPCTAB: Creates the tables
* SPCPKG: Creates the package

Configuring Statspack

How to locate the current Ststapack level?
- Look at table PERFSTST.STATS$SNAPSHOT
or
- Run spreport.sql and you will notice it alongwith the snapids listed

STATS$SNAPSHOT will show level for each Snapshot recorded

Change Level
execute statspack.snap (i_snap_level=> 7, i_modify_parameter=>'true');

Levels >= 0 General Performance Statistics
Levels >= 5 Additional Data: SQL Statements
Levels >= 6 Additional Data: SQL Plans and SQL Plan Usage
Levels >= 10 Additional Statistics: Parent and Child Latches

Using Statspack (gathering data)

sqlplus perfstat
--
-- Take a performance snapshot
--
execute statspack.snap;
--
-- Get a list of snapshots
--
column snap_time format a21
select snap_id,to_char(snap_time,'MON dd, yyyy hh24:mm:ss') snap_time
from sp$snapshot;
--

Running a Performance report
--
-- Run the Statspack report:
--
@?/rdbms/admin/spreport.sql

Locate Hard hitting SQL from Statpack Reposistory
1. Login as PERFSTAT user on database.
It won't work unless U login as PERFSTAT user.

2. Find DBID using
"select dbid from stats$sql_summary"

3. Locate MIN(SNAP_ID) pBgnSnap & MAX(SNAP_ID) pEndSnap from

select min(snap_id),max(snap_id),min(snap_time),max(snap_time) from stats$snapshot
where to_number(to_char(snap_time,'HH24')) > 10 and to_number(to_char(snap_time,'HH24')) < 13 and trunc(snap_time)=trunc(sysdate)

Show All SQL Stmts ordered by Logical Reads

select
e.hash_value "E.HASH_VALUE"
, e.module "Module"
, e.buffer_gets - nvl(b.buffer_gets,0) "Buffer Gets"
, e.executions - nvl(b.executions,0) "Executions"
, Round( decode ((e.executions - nvl(b.executions, 0)), 0, to_number(NULL)
, (e.buffer_gets - nvl(b.buffer_gets,0)) /
(e.executions - nvl(b.executions,0))) ,3) "Gets / Execution"
, Round(100*(e.buffer_gets - nvl(b.buffer_gets,0))/sp920.getGets(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap,'NO'),3) "Percent of Total"
, Round((e.cpu_time - nvl(b.cpu_time,0))/1000000,3) "CPU (s)"
, Round((e.elapsed_time - nvl(b.elapsed_time,0))/1000000,3) "Elapsed (s)"
, Round(e.fetches - nvl(b.fetches,0)) "Fetches"
, sp920.getSQLText ( e.hash_value , 400) "SQL Statement"
from stats$sql_summary e
, stats$sql_summary b
where b.snap_id(+) = :pBgnSnap
and b.dbid(+) = e.dbid
and b.instance_number(+) = e.instance_number
and b.hash_value(+) = e.hash_value
and b.address(+) = e.address
and b.text_subset(+) = e.text_subset
and e.snap_id = :pEndSnap
and e.dbid = :pDbId
and e.instance_number = :pInstNum
order by 3 desc

Show SQL Stmts where SQL_TEXT like '%'
select
e.hash_value "E.HASH_VALUE"
, e.module "Module"
, e.buffer_gets - nvl(b.buffer_gets,0) "Buffer Gets"
, e.executions - nvl(b.executions,0) "Executions"
, Round( decode ((e.executions - nvl(b.executions, 0)), 0, to_number(NULL)
, (e.buffer_gets - nvl(b.buffer_gets,0)) /
(e.executions - nvl(b.executions,0))) ,3) "Gets / Execution"
, Round(100*(e.buffer_gets - nvl(b.buffer_gets,0))/sp920.getGets(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap,'NO'),3) "Percent of Total"
, Round((e.cpu_time - nvl(b.cpu_time,0))/1000000,3) "CPU (s)"
, Round((e.elapsed_time - nvl(b.elapsed_time,0))/1000000,3) "Elapsed (s)"
, Round(e.fetches - nvl(b.fetches,0)) "Fetches"
, sp920.getSQLText ( e.hash_value , 400) "SQL Statement"
from stats$sql_summary e
, stats$sql_summary b
where b.snap_id(+) = :pBgnSnap
and b.dbid(+) = e.dbid
and b.instance_number(+) = e.instance_number
and b.hash_value(+) = e.hash_value
and b.address(+) = e.address
and b.text_subset(+) = e.text_subset
and e.snap_id = :pEndSnap
and e.dbid = 2863128100
and e.instance_number = :pInstNum
and sp920.getSQLText ( e.hash_value , 400) like '%ZPV_DATA%'
order by 3 desc

How to retrieve entire SQL + Execution PLAN from Statspack for a table

To retrieve SQL plan you need to have statspack working on level 7

1. sprepsql.sql
The SQL report (sprepsql.sql) is a report for a specific SQL statement. The SQL report is usually run after examining the high-load SQL sections of the instance health report.The SQL report provides detailed statistics and data for a single SQL statement (as identified by the Hash Value in Statspack report).

2. Hash Value is known
- Select * from STATS$SQLTEXT where hash_value='%from stats pack%' order by piece;

- For an Object first locate the OBJECT_ID

select * from sys.obj$ where name='TRANSACTION'

select snap_time
snap_id,
plan_hash_value,
OBJECT# ,
OBJECT_NAME ,
OPERATION ,
OPTIONS ,
COST ,
IO_COST ,
CARDINALITY ,
POSITION ,
CPU_COST ,
OPTIMIZER ,
SEARCH_COLUMNS ,
BYTES ,
DISTRIBUTION ,
TEMP_SPACE ,
ACCESS_PREDICATES ,
FILTER_PREDICATES
from stats$SQL_PLAN a , STATS$SNAPSHOT b where object#='&&OBJECT_ID' and a.snap_id=b.snap_id;

http://OracleDbaSupport.co.uk is a blog site of Sagar Patil, an independent oracle consultant with a great understanding of how the Oracle database engine & Oracle Applications work together.

I am an Oracle Certified RAC DBA with over ten years experience supporting Production and Development instances of Oracle databases.
- Expert in building 10g RAC systems as well as rman, data guard backup and recovery strategies for production, test and development systems.
- Instructor for developers on Oracle architecture, Oracle9i /10g features, tuning methodologies, database replication, PL/SQL and Oracle Http Server.
- User of Statspack, SQL Trace, OEM Performance Manager, Quest Central, Quest Foglight, Quest SQL impact, Bennchamark factory, Quest SQL optimizer, TOAD and custom scripts.
- Writer of detailed standards & practices for Oracle installs, upgrades, tuning & backups on Oracle Databases and PL/SQL.Developer, designer and implementer of Disaster recovery backup Procedures
- Leader on major projects through all phases of development, testing and Support

I can be hired on a short term notice, to handle strategy, design, implementation, trouble-shooting, DBA cover, remote monitoring, and training.
Please visit www.oracledbasupport.co.uk for your oracle DBA needs.
read more......

Migrating From Microsoft Access to Mysql

First question is do you really need to migrate to MySQL? Using MySQL server as a data storage instead of Microsoft Access offers several benefits: high performance, cross-Platforming, multiple users access, strong security system and much more. So, if you are constructing a complex multi-user system, you defenitiely need to migrate data from Access to MySQL.

But if MS Access data is used by a single user and the data file is simply copied to a new machine when it needs to be moved, there is not much benefit to using MySQL in such a situation.

There is the standard way to migrate Microsoft Access data to MySQL server. MS Access can export its tables to a variety of formats, including ODBC. Using this feature you will be able to export an Access table to MySQL via Connector/ODBC ODBC driver provided by MySQL AB. To migrate MS Access table to MySQL server right-click on the table and select "Export" option. After several steps your data will be exported to MySQL.

Is this method enough for you? If your MS Access database is small and does not have complex structure, the answer is "yes". Otherwise, you should be aware that Access will not export index information with the data. You will need to build indexes on your tables manually after exporting them. Also there are some difference in data types between Microsoft Access and MySQL. One example would be the CURRENCY data type: MySQL does not have a CURRENCY data type, instead it creates a column with the definition DECIMAL(19,4) for the same purpose. While MS Access supports Unicode by defaylt, MySQL does not so tightly bind character sets to field types. Instead it allows each text field to be bound to any number of character sets, including Unicode. Again, you will have to specify it manually if you need to support Unicode in the resulting MySQL database.

Another approach to the migration problem is to use third-party applications that could handle it for you. The world's leader on data processing field, Intelligent Converters developed Access-to-MySQL converter in order to provide users with accurate, fast and easy solution of that conversion task.

Although it is very easy to use, Access-to-MySQL provides enough capabilities to migrate very large and complex MS Access databases. At the same time the migrated results are accurate because the program does all necessary data transformation, supports all MS Access types and attributes, converts all indexes (including relationships between tables), handles multibyte character sets and much more. If you need more control over the migration or just don't have direct access to the destination MySQL server Access-to-MySQL can export data into MySQL dump file.

You can learn more about the product at http://www.convert-in.com/acc2sql.htm
read more......

Database Management Services

In the database management service, there exists a collection of interrelated files and a set of application programs to access and modify these files. The details of data storage, the way of storing data and its maintenance are safely hidden from the users. The database management services allow not only a single user, but it can be accessed by multiple users at the same time. This feature of multiple usages is allowed by almost every database management service provider.

A database management does not involve the problem of data redundancy or inconsistency. Its design is such that it provides the security mechanism to prevent unwanted users from accessing certain parts or whole of the database. For example, tellers in a bank see only that part of database that has information on customer accounts. They cannot access information about salaries of employees. It has many advantages over the file processing system, which stores the permanent records in various files. In this system the problem of redundancy prevails, as files and application programs are created by different programmers over a long period of time.

Besides keeping a tight control over data redundancy, efficient database management services also ensure the following:
* Enforce user defined rules to ensure the integrity of data in table form.

* Has a centralized data dictionary for the storage of information pertaining to data fields and data manipulation.

* Database management ensures that data can be shared across all applications. It enforces data access authorization.

* Database management service has an automatic, intelligent backup and recovery procedure of data.

A database management service also has its share of overheads. To begin with, its initial cost is very high, as it requires setting up of hardware, software and training, as well as, provision of security, concurrency control, and recovery and integrity functions.

Further on the down side, Database management service provides a generalized approach for defining and processing data. On the other hand, file processing system provides well defined database and applications that are simple and are not expected to change.
read more......

Enterprise Database Management

Growing data volumes and increases in regulatory compliance are requiring enterprises to evaluate their data management strategies and implement scalable solutions that solve today’s challenges. Enterprise data management must also integrate into a company’s existing application infrastructure and provide the means to manage data growth while maintaining referential integrity of the application.

Organic Data Growth
E-Business applications have given organization the ability to capture, analyze and retain unparalleled amounts of data about its business, customers, and suppliers. But with these systems are capturing potentially millions of transactions on a daily basis. Over time, data growth is exponential and needs to be actively managed for long term success. By many accounts, stored data in databases is doubling every other year. In addition to the daily capture of information, mergers and acquisitions are putting pressure on IT organizations to support and manage new IT systems. For industries such as High Technology and Financial Services that tend to grow through mergers, acquisition data growth can far exceed the expected organic growth rate.

Downstream Data Growth
Database applications, unlike email and file servers, require multiple copies of production systems to support test and development efforts such as creating versions for patch, test, QA, training and possibly a stand-by copy for disaster recovery purposes. On average, for every production application, IT makes eight copies for production support. As the production database grows, so do all the copies, consuming large quantities of storage. When an application or database needs to be upgraded, additional copies are required to reduce risk associated with the upgrade process. Many times, the need for storage by the database administrators (DBAs) exceeds the allocated storage and storage consumption forecasts. CIOs and IT directors struggle to reduce costs of infrastructure while keeping mission-critical database applications online, operational and current. On average, IT data centers manage at least six mission-critical applications. Multiply the number of applications by the number of copies (6 apps x 8 copies = 42 total) to meet the storage requirement, plus the servers required to support each copy, and the power to support the entire infrastructure, it is no surprise that more than 70 percent of IT budgets are allocated to the database applications even though only 20 percent of the production data is database data. Analysts estimate that email and unstructured content represents approximately 80 percent of production data.

Data Retention Requirements
Corporate policies, Government and regulatory bodies are driving data retention. For example, Healthcare data retention requirements can range from 10 years for patient records to permanent data storage for births and deaths. Sarbanes-Oxley requires corporate financial data be retained for 5 years and under Basel II, Banks needs 7 years of risk data to meet their capital requirements. These regulations were developed to ensure a proper financial and operational record of the business but put a burden on all organizations to not only retain the data but maintain accessibility. Without enterprise data management strategies, organizations will not be able to meet the requirements placed on them, and not being able to produce records is no longer a defensible strategy in regulatory actions or litigation.

Impact of Growth
The expectation of web-based enterprise applications is near instantaneous access. Unchecked data growth can affect all areas of the organization, making it impossible to fully utilize CRM, ERP, or SCM, applications, decreasing productivity and potentially impacting business performance.

Performance
Large volumes of data in the production system slow application response time and reporting processes, especially during critical times such as quarter or year-end close. The net effect of slow response times and limited reporting capability means information needed to make business decisions is limited.

Maintenance and Storage
Maintaining large database applications add complexity, risk and cost to the business. More time is needed to perform routine back-up and maintenance activities. Additionally, large databases require more storage in production and the back-office to create the multiple clones needed for test and development processes. Even though storage and CPU costs are falling, data growth will still tax the IT infrastructure beyond the ability of hardware upgrades to negate them.

Enterprise Data Management
Enterprise Data Management is a proven strategy to manage database growth in an organization. By classifying data according to its value to organization, data that needs to be highly availably can be left in the production server, while underutilized, less valuable data can be moved to more efficient, Tier 2 or lower storage tiers.

Data Archiving
With data archiving, organizations can create and deploy consistent policies for managing, securing and storing data. The result is improved application performance and availability by reallocating under-utilized or inactive data from the production database into a secure online or offline data archive. Ideally, organizations maintain access through the native enterprise application layer to ensure seamless data access for near and long term reporting requirements.

Strategy for Growth
Data growth is inevitable in any growing organization but deploying enterprise data management tools and strategies give organizations the ability to stay ahead of data growth and achieve higher application performance and lower IT costs.

How Solix’s Enterprise Data Management Solutions helps?
Solix Technologies Inc. is a global provider of Enterprise Data Management software solutions for Compliance and Information Lifecycle Management (ILM). The Solix Enterprise Data Management Suite enables organizations to discover, classify and manage structured, semi-structured, and unstructured data, and easily implement tiered storage strategies, while securing, managing and auditing confidential data for compliance and information governance.

With a global client base, Solix is considered a pioneer in providing a complete product suite to manage data across all segments (Application, Email and Documents) in an enterprise. The result is reduced risk, increased productivity and more time available to proactively focus on strategic IT initiatives.
read more......