oracle database password cracker

if you forgot the password for of oracle database, no worry now you have alternate for this

You can easily crack the password for oracle database using plsql script.

Download the plsql code direct from the author link:
http://www.petefinnigan.com/oracle_password_cracker.htm

The cracker works on 9i (r1 and r2), 10g (r1 and r2) and 11gR1.

Please read the detail, execution requirement and other important notes.

Install PostgreSQL ODBC drivers in Windows

If you are trying to create User DSN/ System DSN in ODBC drivers and suddenly you found that ODBC driver string is missing.

 

image 1 - machine not have postgres odbc driver

When you want to connect any third party tool with postgres SQL database, you have to create odbc DSN.

Installing the PostgreSQL ODBC drivers:
way 1:
Download msi package to install odbc from below link
http://www.postgresql.org/ftp/odbc/versions/msi/

Unzip the package and let it install and finish.
way 2:
Download postgres db package
after installation it will ask for
"Stack builder and additional tools" - check this option and  select add-on package "jdbc".

QV27 Checklist for a good qlikview developer

If you are good qlikview developer, and like to work in steps, or good planed work. Then you should have some points in your mind - that should meet with your code.

This can lead to good product and code quality.

Data Model Performance

1. Synthetic keys removed from data model   
2. Ambiguous loops removed from data model   
3. Correct granularity of data   
4. Use of QVDs where possible   
5. Use integers to join tables where possible   
6. Remove system keys/timestamps from data model   
7. Unused fields removed from data model   
8. Remove link tables from very large data models   
9. Remove unneeded snowflaked tables (consolidate)   
10. Break concatenated dim. fields into distinct fields    
11. All QVD reads optimized   
12. Use Autonumber to replace large concatenated keys   

Interface Performance

1. Run QlikView Optimizer to test memory usage   
2. Minimize count distinct functions   
3. Minimize nested Ifs   
4. Minimize string comparisons   
5. Macros minimized or eliminated   
6. Minimize Show Frequency feature   
7. Minimize open objects on sheet   
8. Minimize set analysis against large fact tables   
9. Minimize pivot charts in very large apps   
10. Avoid "Show Frequency" feature on large data   
11. Avoid AGGR function when possible   
12. Avoid IF statements in calculated chart dimensions   
13. Avoid built-in time functions in GUI (inmonth, etc…)   

Design Best Practices                       

1. Use of colors for contrast/focus only               
2. Use of neutral and muted colors                
3. Use of templates/themes where available               
4. Display optimized for user screen resolutions               
5. Design consistency across tabs               
6. Formatting consistency across objects               
7. Most used selections at top - least at bottom               
8. Drop-down selections on all straight/pivot table columns               
9. Developer QV version matches production               
10. Test client types for rendering               
11. Use of Common Variables for expressions               
12. Use calculation conditions on large charts                

                       
Script Best Practices          

1. Naming standards used for columns, tables, variables               
2. Script is well commented - changes date flagged               
3. First tab holds information section               
4. Subject areas each have tab in script               
5. Use of Include files or hidden script for all ODBC connections               
6. All code blocks with comment sections               
7. All file references using UNC naming               
8. Business names for UI fields               
9. Security script in Inlcude file               
10. Turn Generate Logfile option on               
11. UPPER() function used on Section Access fields               
12. Publisher Service Acct added to Section Access               
13. Use numeric flags where possible               

QV26 Naming convention for any qlikview project

Prefixes                               
variables                Starts with a "v"             e.g.    vYear       
Key Fields               Starts with a "%"             e.g.    %companykey       
Flag Fields              Starts with a "_"             e.g.    _IsEnable       
Cycle Group              Starts with a "<"          e.g.    <YearMonth
Drilldown Group          Starts with a ">"          e.g.    >CountryStateCity
Key Field Separator      Separated by "_"              e.g.    Company&'_'&Nbr as Key       
Temp Fileds/Tables       End With "_tmp"               e.g.    Daily_Trans_tmp       
                                   
Field Names                               
Use business names for fields.                       e.g.    Customer Nbr instead of CustNo       
Rename fields in scripts where possible - not in charts and tables                                
                                   
Publisher Naming Standards                               
Abbr.  Abbr. Type     Meaning                       
All    Environment    Applicable to all environments                       
DEV    Environment    Development Environment                       
PRD    Environment    Production Environment                       
TST    Environment    Test Environment                       
APR    Publisher Item    Publisher Access Point Resource                       
JOB    Publisher Item    Publisher Job                       
SDF    Publisher Item    Publisher Source Document Folder Resource                       
TSK    Publisher Item    Publisher task                       
DSR    Publisher Item    Directory Service Resource                       
                                   

QV25 How to tune a qlikview application

We know that Qlikview is data pulling and other operations are quite faster than other tools. Still we need to optimize the qlikview document.

Reload, chart loading are depend upon various factor. Good data model also leads to high speed.

Some below factor we need to take care in document:

Reduce Rows	Eliminate unneeded data volume (rows) from apps (how far back does the app really need to store data?
Reduce Columns	Find unused fields in the data model - use DocumentAnalyzer_V1.5.qvw against your app to do this, then copy the "DROP FIELD" statements that are generated for the unused fields and place these into a new tab in YOUR application, as the VERY LAST tab in the script.
Reduce Distinctness	Reduce Distinctness - Reduce distinct values in your application - use the QlikView Optimizer.qvw against your app to find the fields that are candidates for these changes.   Select the "Symbols" in the Optimizer app to see these (timestamps, system keys, etc..)
Increase efficiency	Convert fields to numeric that are used in compare expressions, like IF(ClientType = 'Direct'….)
Reduce data model complexity	Collapse snowlfaked tables to attain a more pure star schema
Reduce App complexity	Reduce number of tabs/objects in the application, where appropriate
Reduce App complexity	Reduce the number of open charts down to 1 on tabs where this is possible and appropriate
Best Practices	Follow checklist items above in all areas

For Charts
1. Avoid Calculated dimensions - if possible use direct column in dimensions, calculated dimension leads to decrease the performance of table chart, because calculated dimension, do calculation for every row.

QV24 button - action "Toggle Select"

In script, we have Inline table, which contain field one, on which we are going to apply toggle to filter data from reports.

load * inline [
one, data
1,j
1,4
1,info
1,blog
0,spot
0,com
];
now create a sheet object- " button"
    - properties
    - Actions
    - add action
    - "Toggle select" (in field add field "one" and value in input box below field)
   

now add object on sheet "current select "
add object table box, add both fields in it.

now click on the button and see the behaviour in "chart" and "current selection box".

by default in table box it will show all rows including 1,0 . when we switch to toggle button it will show only 1 respective data in table box.
   

QV23 keepchar & purgechar in qlikview

replace equivalent in qlikview:

Want to remove unwanted chars from string?
Qlikview provide us very simple function like "keepchar" and "purgechar".

Keepchar - it will return only string, which we want to keep (we need to mention list of char, that we want to keep)
purgechar - it will remove unwanted chars from string (we need to mention list to char, that we dont want to keep)


examples:
=keepchar ( 'j%4in'(fo!','abcdefghijklmnopqrstuvwxyz0123456789' ) returns 'j4info'
=PurgeChar('j%4in'(fo!', '%(!' &chr(39))

*chr(39) - is used for remove single quote from string.

ROWNUM - Limiting Results and limitations

Rownum is mostly used pseudocolumn column, used for limit result like LIMIT in other databases and also used for TOP N-query.

Some people used rownum in query and ask why it don't return exact result they want. Before using rownum we should really know the working of rownum.

WORKING
ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N. A ROWNUM value is not assigned permanently to a row. 

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row: 

select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure: 

select ..., ROWNUM
  from t
 where 
 group by 
having 
 order by ;

Think of it as being processed in this order:
1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.
That is why a query in the following form is almost return wrong result:(emp list is not top most)

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

LIMITATION 
ROWNUM only work with < in where clause

ROWNUM can not used for range like if you need result between some range

SOLUTION TO THIS use INLINE QUERY

TOP N result: 

below query give us list of top paid employees 

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

export import of mysql database on LINUX machine

How to take export of mysql database  on sql file and after this we are going to import this on new database on mysql (installed on linux machine).

take export of database to sql file_name
current path
"/home/singh/"
(you can choose path your own)
mysqldump -u root -p database_name > file_name.sql

import sql dbbackup to database
- create new database
    mysql - u root -p
    - mysql> create database database_name_new;
    - mysql> use database_name_new;
    - mysql> source /home/singh/file_name.sql

Search String in MYSQL

to search string from mysql database from all tables, than here we have procedure for it. Execute below two queries,
1. create table, which will store the result into it
2. execute procedure on database, from which you want to search string.


create table temp_details (t_db varchar(50), t_table varchar(50) , t_field varchar(50));

DELIMITER $$

DROP PROCEDURE IF EXISTS search_string $$

CREATE PROCEDURE search_string(in_search varchar(50))
READS SQL DATA
BEGIN
    DECLARE trunc_cmd VARCHAR(50);
    DECLARE search_string VARCHAR(250);
    DECLARE db,tbl,clmn CHAR(50);
    DECLARE done INT DEFAULT 0;
    DECLARE COUNTER INT;
    DECLARE table_cur CURSOR FOR
        SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP "',in_search,'"') ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA IN ('schema_name');
    
    # note change schema name to present schema name
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    
    PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";
    EXECUTE trunc_cmd ;
    
    OPEN table_cur;
    table_loop:LOOP
        FETCH table_cur INTO search_string,db,tbl,clmn;
        #Executing the search
        SET @search_string = search_string;
        #SELECT search_string;
        PREPARE search_string FROM @search_string;
        EXECUTE search_string;
        SET COUNTER = @CNT_VALUE;
        #SELECT COUNTER;
        IF COUNTER>0 THEN
            # Inserting required results from search to table
            INSERT INTO temp_details VALUES(db,tbl,clmn);
        END IF;
        IF done=1 THEN
            LEAVE table_loop;
        END IF;
    END LOOP;
    
    CLOSE table_cur;
    
    #Finally Show Results
    SELECT concat("SELECT * FROM ",t_table, " WHERE ", t_field, " REGEXP '", in_search, "';") FROM temp_details;
END $$
DELIMITER ;

call search_string('abc');

it will return some set of queries, which we can directly execute to check string respective value in table.

related link:

SEARCH STRING IN POSTGRES

http://j4info.blogspot.in/2014/10/string-search-in-database-postgres.html