Sybase Session -6 Document

SYBASE SESSION - 6 DOCUMENT

BCP UPDATES AND JOINS

___________________________________________________________________________________________________ Copyrights Reserved Page 1 of 9

Topics Covered: 

Bulk Copy Program



Definition copy utility



Interactive SQL (isql) utility



Updates and performance



Joins

___________________________________________________________________________________________________ Copyrights Reserved Page 2 of 9

Bulk copy program: The

bulk copy program is a tool that transfers data between a table and an operating system file at very high speeds. It is the most common way to load large amounts of data in to an sql server table. It is also frequently used to transfer data from one database to other database at a very high speeds The bcp program has over 25 options available. It is important to know the capabilities and shortcomings of bcp .

Tools in Sybase: There

are three major tools provided by Sybase for administering, maintaining, and accessing the sql server. Bcp Defncopy isql

Bcp: The

bulk copy program is a tool that transfers data between a table and an operating system file at very high speeds.

Defncopy: It

is used to import and export object-creation statements for views, triggers, rules and defaults and procedures.

Isql: This

is the standard interface provided by Sybase, which also often used for batch program execution.

Definition copy utility: This

can be used to copy object-creation statements from the database system tables in to an operating system file or to create an object in a database using an existing file. Defncopy can be used to copy defaults, rules, stored procedures, triggers, or views but cannot be used for tables or indexes. Defncopy works on all those objects on which you can perform.

___________________________________________________________________________________________________ Copyrights Reserved Page 3 of 9

Defncopy parameters: We have some important defncopy parameters. They are Flag

-a -p Flag -s Flag -z Flag

Flag -a: This

is the charset used by the display device. This defncopy program can be running on a different machine than the display. Flag -p: This

is the password for the login name specified. If this option is not specified, defncopy prompts for it. Flag -s: This

is the name of the server to which to connect. If the server name is not

provided, it uses the $DSQUERY value. Flag -z: This

tells defncopy to put messages and promotes in a language other than the default language for the server.

Interactive SQL (isql) utility: This

isql is the generic interactive SQL execution utility for the server and it is used to execute activities such as creating objects, testing, inserting data and selecting information. Isql often is run as a batch command interpreter rather than an interactive tool Files are created that are read in to isql for processing with the output being directed to the terminal or an operating system file.

___________________________________________________________________________________________________ Copyrights Reserved Page 4 of 9

Isql parameters: Like defncopy parameters we have some important isql parameters. They are Flag Flag Flag Flag Flag

-A -F -I -Y -S

Flag -A: This

is used to specify an editor other than the default editor. For example if Vi is the default editor . Flag -F: This

is used to enable the fips flagger, which alerts the user to any sql that is considered non-ascii standard. Flag -I: This

is the name of the interface file to use when trying to find a server to which to connect. If this option is not provided, isql looks for a file named interfaces in the directory. Flag -Y: This

is the directory in which to look for the interface file, if other than the default $Sybase directory. Flag S: This

is the name of the server to which to connect. If the server name is not provided isql uses the $dsquery value if it exist or Sybase. This name should be found in the interface file used by isql. Flag -F: This

is used to enable the fips flagger, which alerts the user to any sql that is

___________________________________________________________________________________________________ Copyrights Reserved Page 5 of 9

considered non-ascii standard.

Updates: All

updates within the sql server are essentially a delete followed by an insert With a clustered index on the table the row is reinserted to the approximate location on the appropriate data page relative to itd physical sort order. Without a clustered index the row is inserted into one of the three locations The same physical location on the same data page The same data page if there is room The last page in the heap

In sql server there are two distinct types of updates. Deferred

updates Direct updates

Deferred updates: A

deferred update is a multi step process, which occurs when the conditions are not met for direct update to occur.

Direct updates always required for the following Updates

that include a join Updates to columns used for referential integrity

Direct updates: Direct

updates can be performed either in -place or not-in-place. A direct update not-in-place is still a delete followed by a insert but the updates can be applied in a single pass. A

direct update not in place can be classified as a cheap direct update or an expensive update. Direct updates can occur in two ways. They are updates-in-place updates-not-in-place

Joins: An

sql join clause combines records from two tables in a database. It creates a set that can be saved as a table. ___________________________________________________________________________________________________ Copyrights Reserved Page 6 of 9

A

join is a means for combining fields from two tables by using values common to each.

ANSI standard sql specifies four types of joins. They are INNER OUTER LEFT RIGHT.

In special cases, a table (base table, view, or joined table) can JOIN to itself in a self-join. Inner join: An

inner join requires each record in the two joined tables to have a matching record An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. Outer Join: An

outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which tables one retains the rows.

Right outer joins: A

right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate) right

outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A. Left outer joins: ___________________________________________________________________________________________________ Copyrights Reserved Page 7 of 9

The

result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). That

means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

1) What is Bcp? 2) What are the tools provided by bcp? 3) What are the modes of operation? 4) What is the storage length of BCP? 5) What are the functions of the following in BCP? -

T -b -e -A 7) Explain the Define copy options listed below datafile in

| out

-p -x

8) What are the errors while occurring during bcp in and out? 9) What do u mean by batch? 10) Say true or false -E option is use to handle error in BCP. -M option default value is 0. BCP does not invoke Rules, Constraints, Defaults, Trigger. 11) Explain the command isql

–Usa -P -S-i-o

12) What are the two distinct types in updates? 13) What are the types of joins? ___________________________________________________________________________________________________ Copyrights Reserved Page 8 of 9

Reference Book: Sybase SQL Server 11 by Ray Rankins Jeffrey R. Garbus David Solomon

___________________________________________________________________________________________________ Copyrights Reserved Page 9 of 9