The easiest way to solve your "forgotten root password" problem is to boot your Linux in the single-user mode, namely at the "lilo"prompt (during bootup) type:
linux single
This will make you "root" without asking for a password. Now, being root, you may change the root password using this command (no knowledge of the old password required):
passwd
If it strikes you as insecure, that's because no computer system is secure if other people have physical access to your hardware. I did not like the "linux single" hole on my home computer and plugged it by adding the following lines to my /etc/lilo.conf file (at the end of the "image=" section):
password="my_password"
restricted
[This "lilo" password is required when, at the LILO prompt during bootup, somebody enters the word "linux" with any parameter (normal bootup without any parameters will still be possible without a password).] For the changes to /etc/lilo.conf to take effect, I must re-run the command lilo . Since my lilo password is not encrypted, I must make /etc/lilo.conf readable only for root:
chmod 600 /etc/lilo.conf
Saturday, December 5, 2009
Fetch n rows in DB2
DB2 Version 7 provides an easy way to limit the results of a SELECT statement using a new clause – the FETCH FIRST n ROWS clause. When the FETCH FIRST n ROWS clause is specified, DB2 will limit the number of rows that are fetched and returned by a SELECT statement. This Version 7 approach requires SQL only and is quite simple and efficient. The FETCH FIRST n ROWS ONLY clause is appended right to the end of the SELECT statement. It is used as follows:
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY
FROM DSN8710.EMP
ORDER BY SALARY DESC
FETCH FIRST 10 ROWS ONLY;
Of course, the value can be any number – not just 10.
For example, to retrieve only the top 4 salaries you would code:
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY
FROM DSN8710.EMP
ORDER BY SALARY DESC
FETCH FIRST 4 ROWS ONLY;
This is the simplest and probably the most elegant solution for limiting the number of rows returned by a DB2 query. This clause is different from the OPTIMIZE FOR n ROWS clause that has been available for several releases of DB2 now. The FETCH FIRST n ROWS ONLY clause will limit the number of rows returned to the specified number, n. Remember that the OPTIMIZE FOR n ROWS clause does not impact the number of rows returned, but is used only by the optimizer for optimizing SQL.
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY
FROM DSN8710.EMP
ORDER BY SALARY DESC
FETCH FIRST 10 ROWS ONLY;
Of course, the value can be any number – not just 10.
For example, to retrieve only the top 4 salaries you would code:
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY
FROM DSN8710.EMP
ORDER BY SALARY DESC
FETCH FIRST 4 ROWS ONLY;
This is the simplest and probably the most elegant solution for limiting the number of rows returned by a DB2 query. This clause is different from the OPTIMIZE FOR n ROWS clause that has been available for several releases of DB2 now. The FETCH FIRST n ROWS ONLY clause will limit the number of rows returned to the specified number, n. Remember that the OPTIMIZE FOR n ROWS clause does not impact the number of rows returned, but is used only by the optimizer for optimizing SQL.
Wednesday, December 2, 2009
Optimistic Concurrency
Optimistic Concurrency
There are four popular strategies to dealing with optimistic concurrency:
There are four popular strategies to dealing with optimistic concurrency:
- Do Nothing.
- Check for changes to all fields during update.
- Check for changes to modified fields during update.
- Check for changes to timestamp ( rowversion ) during update.
All of these strategies have to deal with the shaping of the Update T-SQL Command sent to the database during the updating of the data. The examples below are not very detailed on purpose and assume a basic understanding of ADO.NET. Below shows the strategies from a view point of 30,000 ft high.
Optimistic Concurrency on Update Strategy #1 - Do Nothing
The simplest strategy for dealing with concurrency issues during the updating of data is to do nothing.
The update command will not check for any changes in the data, only specify the primary key of the record to be changed. If someone else changed the data, those changes will more than likely be overwritten:Update Product
Set
Name = @Name Where
ID = @ID
One would hope that this means either 1) the application is a single-user application, or 2) the chance of multi-user update collisions is very unlikely and the repercussions of overwriting data is negligible.
Optimistic Concurrency on Update Strategy #2 - Check All Fields
With this strategy, the update command will check that all fields in the row ( usually minus BLOB fields ) are equal to their original values when peforming the update to assure no changes have been made to the original record. A check of the return value of the ExecuteNonQuery Command will tell you if the update actually took place. The return value of the ExecuteNonQuery Command is typically the number of rows affected by the query.Update Product
Set
Name = @Name,
Where
ID = @ID
AND
Name = @OriginalName
AND
Price = @OriginalPrice
This is essentially what CommandBuilder creates when using DataSets and is a strategy that doesn't want to see any changes to the data.
Optimistic Concurrency on Update Strategy #3 - Check Only Changed Fields
Rather than checking all fields in the row to make sure they match their original value, this strategy checks only those fields that are being updated in the command.Update Product
Set
Name = @Name
Where
ID = @ID
AND
Name = @OriginalName
This strategy only cares that it is not overwriting any data and could care less that other fields in the record may have been changed. This could create an interesting combination of data in the row.
Optimistic Concurrency on Update Strategy #4 - Implement Timestamp
SQL Server has a timestamp ( alias rowversion ) field that is modified everytime a change is made to a record that contains such a field. Therefore, if you add such a field to a table you only have to verify the timestamp record contains the same original value to be assured none of the fields have been changed in the record.Update Product
Set
Name = @Name
Where
ID = @ID
AND
TimestampID = @TimestampID
This is the same as Strategy #2 above without the need for checking all fields.
Concurrency Control
In general, there are three common ways to manage concurrency in a database:
Pessimistic concurrency control: A row is unavailable to users from the time the record is fetched until it is updated in the database.
Optimistic concurrency control: A row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.
"Last in wins": A row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.
ADO.NET and Visual Studio use optimistic concurrency, because the data architecture is based on disconnected data. Therefore, you need to add business logic to resolve issues with optimistic concurrency.
If you choose to use optimistic concurrency, there are two general ways to determine if changes have occurred: the version approach (true version numbers or date-time stamps) and the saving-all-values approach.
The Version Number Approach
In the version number approach, the record to be updated must have a column that contains a date-time stamp or version number. The date-time stamp or a version number is saved on the client when the record is read. This value is then made part of the update.
One way to handle concurrency is to update only if value in the WHERE clause matches the value on the record. The SQL representation of this approach is:
UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE DateTimeStamp = @origDateTimeStamp
Alternatively, the comparison can be made using the version number:
UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE RowVersion = @origRowVersionValue
If the date-time stamps or version numbers match, the record in the data store has not changed and can be safely updated with the new values from the dataset. An error is returned if they don't match. You can write code to implement this form of concurrency checking in Visual Studio. You will also have to write code to respond to any update conflicts. To keep the date-time stamp or version number accurate, you need to set up a trigger on the table to update it when a change to a row occurs.
The Saving-All-Values Approach
An alternative to using a date-time stamp or version number is to get copies of all the fields when the record is read. The DataSet object in ADO.NET maintains two versions of each modified record: an original version (that was originally read from the data source) and a modified version, representing the user updates. When attempting to write the record back to the data source, the original values in the data row are compared against the record in the data source. If they match, it means that the database record has not changed since it was read. In that case, the changed values from the dataset are successfully written to the database.
Each data adapter command has a parameters collection for each of its four commands (DELETE, INSERT, SELECT, and UPDATE). Each command has parameters for both the original values, as well as the current (or modified) values.
Pessimistic concurrency control: A row is unavailable to users from the time the record is fetched until it is updated in the database.
Optimistic concurrency control: A row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.
"Last in wins": A row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.
ADO.NET and Visual Studio use optimistic concurrency, because the data architecture is based on disconnected data. Therefore, you need to add business logic to resolve issues with optimistic concurrency.
If you choose to use optimistic concurrency, there are two general ways to determine if changes have occurred: the version approach (true version numbers or date-time stamps) and the saving-all-values approach.
The Version Number Approach
In the version number approach, the record to be updated must have a column that contains a date-time stamp or version number. The date-time stamp or a version number is saved on the client when the record is read. This value is then made part of the update.
One way to handle concurrency is to update only if value in the WHERE clause matches the value on the record. The SQL representation of this approach is:
UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE DateTimeStamp = @origDateTimeStamp
Alternatively, the comparison can be made using the version number:
UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE RowVersion = @origRowVersionValue
If the date-time stamps or version numbers match, the record in the data store has not changed and can be safely updated with the new values from the dataset. An error is returned if they don't match. You can write code to implement this form of concurrency checking in Visual Studio. You will also have to write code to respond to any update conflicts. To keep the date-time stamp or version number accurate, you need to set up a trigger on the table to update it when a change to a row occurs.
The Saving-All-Values Approach
An alternative to using a date-time stamp or version number is to get copies of all the fields when the record is read. The DataSet object in ADO.NET maintains two versions of each modified record: an original version (that was originally read from the data source) and a modified version, representing the user updates. When attempting to write the record back to the data source, the original values in the data row are compared against the record in the data source. If they match, it means that the database record has not changed since it was read. In that case, the changed values from the dataset are successfully written to the database.
Each data adapter command has a parameters collection for each of its four commands (DELETE, INSERT, SELECT, and UPDATE). Each command has parameters for both the original values, as well as the current (or modified) values.
Import CSV file into SQL Server
Create TestTable
USE TestDataGOCREATE TABLE CSVTest(ID INT,FirstName VARCHAR(40),LastName VARCHAR(40),BirthDate SMALLDATETIME)GO
Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULKINSERT CSVTestFROM 'c:\csvtest.txt'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
GO
--Check the content of the table.
SELECT *FROM CSVTest
GO
USE TestDataGOCREATE TABLE CSVTest(ID INT,FirstName VARCHAR(40),LastName VARCHAR(40),BirthDate SMALLDATETIME)GO
Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULKINSERT CSVTestFROM 'c:\csvtest.txt'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
GO
--Check the content of the table.
SELECT *FROM CSVTest
GO
How To Use SQL Server Reporting Services
Business development often requires analysis and data mining. Determining trends with which Customers and other stake holders deal with a business are of sole importance to the business. In fact, helping a businessman to grow his business has contributed a lot in rapid growth of IT industry.
Microsoft SQL Server Reporting services provide a solution to those tasked in finding business trends. Briefly speaking:
Reporting services lets you create rich reports and format them based on your requirements.
You can create interactive reports using parameters. Also, RS comes with an API to generate complex reports.
You can assign permissions to specific users for viewing reports.
The phenomenon goes like:
a) As a developer, you will first need to create .RDL (Report Definition files) using SQL Server Business Intelligence studio. This file has xml syntax to define your report and in this file you can specify the data source of your report and how the report will be displayed.
b) Once you are done with creating RDL File, you will need to deploy it on Report Server which manages all reports and information kept in reports on backend. Some of its tasks involve keeping metadata of reports, Data source information for reports and cached reports. All of this information is kept in SQL Server databases which you will configure as part of configuring Reporting services.
c) Once you have developed and deployed reports on report server, users can view reports using report manager which uses report server on backend.
Creating a report
1. Open SQL Server Business Intelligence studio and create a new report server wizard project.
You can also create Report Server project instead but at this stage, it is recommended you start with a wizard project.
2. Once you have created the project, you can specify the data source from where to bring data for our report.
3. In the next step, you can specify the query to fetch data which would be used in your report.
4. Next comes different options for formatting your report and that can be done using your preference as to how you want to display your data.
5. Once you have report defined via wizard, you will be able to edit it using Business Intelligence studio.
6. You can define the layout of report using the toolbox at the left. And before you build and deploy your report, you can preview it here as well. Note that a report to be accessible in another project, say in ASP.Net website on developer machine, you would need to deploy the report on report server. Also, any change in report would require you to re-deploy the report in order to reflect the latest change.
7. This completes our process of creating a report and deploying it on report server.
Microsoft SQL Server Reporting services provide a solution to those tasked in finding business trends. Briefly speaking:
Reporting services lets you create rich reports and format them based on your requirements.
You can create interactive reports using parameters. Also, RS comes with an API to generate complex reports.
You can assign permissions to specific users for viewing reports.
The phenomenon goes like:
a) As a developer, you will first need to create .RDL (Report Definition files) using SQL Server Business Intelligence studio. This file has xml syntax to define your report and in this file you can specify the data source of your report and how the report will be displayed.
b) Once you are done with creating RDL File, you will need to deploy it on Report Server which manages all reports and information kept in reports on backend. Some of its tasks involve keeping metadata of reports, Data source information for reports and cached reports. All of this information is kept in SQL Server databases which you will configure as part of configuring Reporting services.
c) Once you have developed and deployed reports on report server, users can view reports using report manager which uses report server on backend.
Creating a report
1. Open SQL Server Business Intelligence studio and create a new report server wizard project.
You can also create Report Server project instead but at this stage, it is recommended you start with a wizard project.
2. Once you have created the project, you can specify the data source from where to bring data for our report.
3. In the next step, you can specify the query to fetch data which would be used in your report.
4. Next comes different options for formatting your report and that can be done using your preference as to how you want to display your data.
5. Once you have report defined via wizard, you will be able to edit it using Business Intelligence studio.
6. You can define the layout of report using the toolbox at the left. And before you build and deploy your report, you can preview it here as well. Note that a report to be accessible in another project, say in ASP.Net website on developer machine, you would need to deploy the report on report server. Also, any change in report would require you to re-deploy the report in order to reflect the latest change.
7. This completes our process of creating a report and deploying it on report server.
SQL Server Reporting Services
- SQL Server Reporting Services, which is popularly known as SSRS.
- Introduced with SQL Server 2000. SSRS is an enterprise-wide centrally managed Reporting Solution.
- It's a Server side reporting platform and comes with tools for creating, managing, and viewing reports.
- It offers web-enabled report access and administration functionality.
- Publish reports in various formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
- The data for reports can come from relational or multidimensional data from SQL Server, Analysis Services, Oracle, XML data sources, or any Microsoft .NET data provider, such as ODBC or OLE DB.
- SQL Server Reporting Services provides a middle-tier server that runs under Microsoft Internet Information Services (IIS)
The main participants in the SSRS ecosystem are:
- End Users: Users who are interested in generated reports
- Administrators: Users responsible for maintaining the SSRS services and Report Manager
- Designers: Users responsible for designing the reports
- Report Manager: Web-based administration tool for SSRS
- Report Services: Core SSRS functionality deployed as Windows Service
- Report Designers: Tools for designing and previewing reports
- Report Server Database: Data store where configuration data for SSRS is stored
- Report Data: Business data store for data published using reports
- Client Applications: Custom applications integrating with SSRS using Web Service endpoints of SSRS
- Mail Server: Integrates with SSRS to send reports to subscribers
Following are the main SSRS feature areas:
Creation
Parameterized Reports
Data Connection Configuration
Access & Delivery of Reports
Report Formats
Two main tools are available for creating report definitions: Report Designer and Report Builder.
Report Designer: Fully featured and powerful tool that runs in Business Intelligence Development Studio (BIDS) of Visual Studio. It can be used to create simple to complex reports that include using custom assemblies for custom functionality.
Report Builder: A quick, simple report building tool that works based on model data sources. It is used mainly to create ad hoc tabular reports. Drag and drop the fields on the template from the model data sources. It is integrated into Report Manager of SSRS.
Creating a report involves two main steps:
Design and preview the report using the above designers. These are called Report definitions and are stored in .rdl files that are in XML format.
Deploy the report definition on the report server. SSRS partially compiles the report definitions and stores them in an SSRS database. These are called Published Reports.
Parameterized Reports
Parameters in reports are mainly supported for three kinds of scenarios:
To select data from a data source based on parameters that can be taken as input from the client also.
To filter the data retrieved from the data source.
To selectively format the report. For example, the report can be based on parameter values showing a particular column or not.
There are two kinds of parameters supported:
Query Parameters: Used to filter data.
Report Parameters: Mainly used for selective layout formatting of a report.
Subscribe to:
Posts (Atom)